On 02/12/2015 20:25, cevian wrote: > Hi all, > Hello,
> 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) The sort pushdown for postgres_fdw has been committed a few weeks ago for 9.6, see http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f18c944b6137329ac4a6b2dce5745c5dc21a8578 > 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? > For now, I don't see any other solution than executing a remote query with the dblink extension: http://www.postgresql.org/docs/current/static/contrib-dblink-function.html Regards. > 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. > > -- Julien Rouhaud http://dalibo.com - http://dalibo.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers