Thread: Missed LIMIT clause pushdown in FDW API
Hello, As far as I know there is no LIMIT clause pushdown in FDW API. Is there some reasons not to support LIMIT clause pushdown? Is there bug-report regarding this (didn't found it though) or should it be filled down?
On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov <alexander.v.reshetov@gmail.com> wrote: > Hello, > > As far as I know there is no LIMIT clause pushdown in FDW API. > > Is there some reasons not to support LIMIT clause pushdown? > Is there bug-report regarding this (didn't found it though) > or should it be filled down? Working for me on 9.5 with postgres_fdw... merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
> <alexander.v.reshetov@gmail.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.
regards, tom lane
On Wed, Mar 30, 2016 at 8:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>> <alexander.v.reshetov@gmail.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
Tom, it's really good news. Thanks! For now as workaround I think that it's possible to add additional column in table. In this way it would be possible to scan only needed part of solumn in storage. While quals is pushed down it will be possible to limit like this select * from table where fake_column == 1 So this fake column would be used as flag for FDW engine to limit query to one result. Of course it can't replace LIMIT, but could help to use at least lateral join with “limit 1” method. But I'm not sure that some results would not be cached in this situation. Will this workaround work as expected? Or maybe there is some other possible temporary solution (at least for specified lateral join)? On Wed, Mar 30, 2016 at 4:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov >> <alexander.v.reshetov@gmail.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. > > regards, tom lane