Hi I am playing around with postgres_fdw and found that the following code ...
---------------------------------- 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'); testdb=# explain analyse select * from onemillion_pgfdw where id in (select id from onemillion where data > '0.9' limit 100); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=122.49..10871.06 rows=500000 width=44) (actual time=4.269..93.444 rows=100 loops=1) -> HashAggregate (cost=22.06..23.06 rows=100 width=4) (actual time=1.110..1.263 rows=100 loops=1) -> Limit (cost=0.00..20.81 rows=100 width=4) (actual time=0.038..1.026 rows=100 loops=1) -> Seq Scan on onemillion (cost=0.00..20834.00 rows=100115 width=4) (actual time=0.036..0.984 rows=100 loops=1) Filter: (data > '0.9'::text) Rows Removed by Filter: 805 -> Foreign Scan on onemillion_pgfdw (cost=100.43..108.47 rows=1 width=29) (actual time=0.772..0.773 rows=1 loops=100) Total runtime: 93.820 ms (8 rows) Time: 97.283 ms ------------------------------ ... actually performs 100 distinct "SELECT * FROM onemillion WHERE id = $1" calls on "remote" side. Is there a way to force it to prefer a plan where the results of (select id from onemillion where data > '0.9' limit 100) are passed to FDW as a single IN ( = ANY(...)) query and are retrieved all at once ? If not, how hord would it be to add this feature ? -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers