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
