Re: Postgres_FDW optimizations

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