Re: Missed LIMIT clause pushdown in FDW API

On Wed, Mar 30, 2016 at 8:33 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>> <alexander(dot)v(dot)reshetov(at)gmail(dot)com> wrote:
>>> As far as I know there is no LIMIT clause pushdown in FDW API.
>>> Is there some reasons not to support LIMIT clause pushdown?
>
> It was impossible to do it before 3fc6e2d7f5b652b4, committed 3 weeks
> ago. Now that that infrastructure exists, someone might look into
> using it for this purpose ... but not before 9.7 at the earliest.
>
>> Working for me on 9.5 with postgres_fdw...
>
> Really? It's true that postgres_fdw won't fetch more rows than it
> actually needs from the remote --- but that's not the same as telling
> the remote planner to prefer a fast-start plan.

I guess was underthinking it. A quick test showed:

castaging=# explain analyze select count(*) from tblapt;
QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=220.92..220.93 rows=1 width=0) (actual
time=753.287..753.287 rows=1 loops=1)
-> Foreign Scan on tblapt (cost=100.00..212.39 rows=3413 width=0)
(actual time=1.753..748.887 rows=64284 loops=1)
Planning time: 0.063 ms
Execution time: 754.636 ms
(4 rows)

Time: 756.746 ms
castaging=# explain analyze select * from tblapt limit 1;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Limit (cost=100.00..100.26 rows=1 width=1839) (actual
time=15.504..15.504 rows=1 loops=1)
-> Foreign Scan on tblapt (cost=100.00..111.29 rows=43
width=1839) (actual time=15.503..15.503 rows=1 loops=1)
Planning time: 0.131 ms
Execution time: 16.615 ms
(4 rows)

Time: 18.619 ms

However, tailing the query log on the remote server, I see that it is
using DECLARE/FETCH and aborting in the limit case. So I was tricked
-- this isn't LIMIT pushdown.

merlin