From: | cevian <cevian(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Postgres_FDW optimizations |
Date: | 2015-12-02 19:25:15 |
Message-ID: | 1449084315624-5875911.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2015-12-02 19:26:00 | Re: Using quicksort for every external sort run |
Previous Message | Alvaro Herrera | 2015-12-02 19:08:21 | Re: broken tests |