Re: Postgres_FDW optimizations - Mailing list pgsql-hackers

Hi all,

I have a question about postgres_fdw optimizations/pushdown:

I have the following code running on 9.5beta2 (same format as
previous/related message for consistency)
CREATE EXTENSION postgres_fdw;
CREATE SERVER loop foreign data wrapper postgres_fdw
  OPTIONS (port '5432', dbname 'testdb');
CREATE USER MAPPING FOR PUBLIC SERVER loop;

create table onemillion (
    id serial primary key,
    inserted timestamp default clock_timestamp(),
    data text
);

insert into onemillion(data) select random() from
generate_series(1,1000000);

CREATE FOREIGN TABLE onemillion_pgfdw (
    id int,
    inserted timestamp,
    data text
) SERVER loop
OPTIONS (table_name 'onemillion',
         use_remote_estimate 'true');

explain verbose select * from onemillion_pgfdw order by id limit 1;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=43434.00..43434.00 rows=1 width=30)
   Output: id, inserted, data
   ->  Sort  (cost=43434.00..45934.00 rows=1000000 width=30)
         Output: id, inserted, data
         Sort Key: onemillion_pgfdw.id
         ->  Foreign Scan on public.onemillion_pgfdw  (cost=100.00..38434.00
rows=1000000 width=30)
               Output: id, inserted, data
               Remote SQL: SELECT id, inserted, data FROM public.onemillion

This is obviously highly inefficient. The sort and limit should be pushed
down to the foreign node, especially on such a simple query. I have 3
questions:

1) Is this the expected stated of the fdw optimizations for now, or is it a
bug?

2) Is anybody working on this type of pushdown right now (I would be more
than willing to collaborate on a patch)

3) Is this possible to fix with with views/rules/triggers/different query. I
couldn't find a way. Relatedly, is there a way to explicitly specify an
explicit remote query to run through the fdw?

Thanks,
Matvey Arye
Iobeam, Inc.




--
View this message in context: http://postgresql.nabble.com/Postgres-FDW-optimizations-tp5875911.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers