performance not as expected

[zheffie]

I'm not reaching the expected performance levels

test table trips with 297k records

test query: select count(*) from trips

native postgres = 416ms native monetdb = 0.479ms postgres using monet_fdw on the foreign table = 15s

Any idea what could be going on?

[snaga]

Pushing-down aggregation operations to the remote monetdb has not been implemented yet. It means all rows in your foreign table are supposed to be transferred to your PostgreSQL when you execute an ad-hoc query.

So, you need to define a foreign table with pre-defined query, like "q12" in the test script, to execute aggregation on your remote monetdb. https://github.com/snaga/monetdb_fdw/blob/master/test_monetdb_fdw.sh

CREATE FOREIGN TABLE q12 (
        l_shipmode text,
        high_line_count bigint,
        low_line_count bigint
) SERVER monetdb_server
OPTIONS (host 'localhost', port '50000', user 'monetdb', passwd 'monetdb', dbname 'dbt3', query '
select l_shipmode,
        sum(case when o_orderpriority = ''1-URGENT'' or o_orderpriority = ''2-HIGH'' then 1 else 0 end) as high_line_count,
        sum(case when o_orderpriority <> ''1-URGENT'' and o_orderpriority <> ''2-HIGH'' then 1 else 0 end) as low_line_count
   from orders, lineitem
  where o_orderkey = l_orderkey and l_shipmode in (''TRUCK'', ''REG AIR'')
    and l_commitdate < l_receiptdate and l_shipdate < l_commitdate
    and l_receiptdate >= date ''1994-01-01''
    and l_receiptdate < date ''1995-01-01''
  group by l_shipmode
  order by l_shipmode;
'
);

BTW, I'm going to implement push-down this summer.

[zheffie]

Ok, that is what I suspected.

Nevertheless, many thanks for your efforts on this. I look forward to summer.