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.00rows=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.