Thread: FDW LIM IT pushdown

FDW LIM IT pushdown

From
Michał Kłeczek
Date:
Hello hackers,

First timer here with a question:

I’ve searched through various e-mail threads and documents and could not find if pushdown of LIMIT clauses to FDW is
implemented.
Seen some conversation about that a couple of years ago when that was treated as a feature request but nothing  since
then.
Citus does that according to its documentation but for now we are trying to base our solution on “plain” Postgres.

If it is not implemented - could you, guys, provide me with some pointers to source code where I could look at and try
toimplement that? 


Thanks,
Michal


Re: FDW LIM IT pushdown

From
Michał Kłeczek
Date:
Sorry, I wasn’t precise - my question is about foreign partitions - LIMIT on ordinary tables is supported.

Thanks,
Michal

> On 6 Oct 2023, at 18:02, Michał Kłeczek <michal@kleczek.org> wrote:
>
> Hello hackers,
>
> First timer here with a question:
>
> I’ve searched through various e-mail threads and documents and could not find if pushdown of LIMIT clauses to FDW is
implemented.
> Seen some conversation about that a couple of years ago when that was treated as a feature request but nothing  since
then.
> Citus does that according to its documentation but for now we are trying to base our solution on “plain” Postgres.
>
> If it is not implemented - could you, guys, provide me with some pointers to source code where I could look at and
tryto implement that? 
>
>
> Thanks,
> Michal




Re: FDW LIM IT pushdown

From
Ashutosh Bapat
Date:
Hi Michal,


On Fri, Oct 6, 2023 at 9:34 PM Michał Kłeczek <michal@kleczek.org> wrote:
>
> Hello hackers,
>
> First timer here with a question:
>
> I’ve searched through various e-mail threads and documents and could not find if pushdown of LIMIT clauses to FDW is
implemented.
> Seen some conversation about that a couple of years ago when that was treated as a feature request but nothing  since
then.
> Citus does that according to its documentation but for now we are trying to base our solution on “plain” Postgres.
>
> If it is not implemented - could you, guys, provide me with some pointers to source code where I could look at and
tryto implement that? 
>

I started looking at code from grouping_planner and reached
create_ordinary_grouping_paths(). It calls
create_partitionwise_grouping_paths() to push aggregate and grouping
down into partitions and from there it is pushed down into FDW.
Looking at create_limit_path(), I don't similar treatment. So there
may be some cases where we are not pushing down final LIMIT.

However create_append_path() uses PlannerInfo::limit_tuples or
root->limit_tuples when creating append path node. So either it's
being used for costing or for pushing it down to the partitions.

This isn't a full answer, but I hope these pointers would help you.

--
Best Wishes,
Ashutosh Bapat