Re: [GENERAL] Missed LIMIT clause pushdown in FDW API

[Alexander Reshetov]

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  wrote:
> Merlin Moncure  writes:
>> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>>  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Missed LIMIT clause pushdown in FDW API

[Merlin Moncure]

On Wed, Mar 30, 2016 at 8:33 AM, Tom Lane  wrote:
> Merlin Moncure  writes:
>> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>>  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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Missed LIMIT clause pushdown in FDW API

[Tom Lane]

Merlin Moncure  writes:
> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Missed LIMIT clause pushdown in FDW API

[Merlin Moncure]

On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Missed LIMIT clause pushdown in FDW API

[Alexander Reshetov]

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?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general