Hi All,
Attached is a second version of the patch.
The goal is to:
1. Apply LIMIT as early as possible - especially to apply LIMIT in partition scans
2. Enable LIMIT pushdown for FDW partitions.
Main idea of the patch is:
1. Wrap children of Append and MergeAppend paths in LimitPaths.
2. Let FDW extension handle limit pushdown
The changes are mainly in pathnode.c:
- Introduced a new function: pushdown_limit() used by planner instead of create_limit_node
- pushdown_limit handles MergeAppend, Append and ForeignScan nodes specially
- it falls back to create_limit_node for other path types
Changes in fdw:
- added a new FDW operation PushdownLimitNode
- this operation is called by pushdown_limit in pathnode.c
Changes in postgres_fdw.c
- Added stub implementation of PushdownLimitNode operation that delegates to create_limit_node wrapping original
ForeignPathnode
I am going to work on tests right now as (obviously) they are failing due to different plans.
As this is my first time I dig into the internals of Postgres I would be really grateful for friendly review and some
directions- I am not sure it the approach is the right one.
The need for this is real: we are struggling with slow queries on partitioned tables - the business requirements are
suchthat the only way to avoid index scans yielding many records is to apply LIMIT early and not execute partition
scansat all if enough rows are produced.
Kind regards,
Michal
> On 7 Oct 2023, at 12:01, Michał Kłeczek <michal@kleczek.org> wrote:
>
> Hi All,
>
> Attached is a draft patch implementing LIMIT pushdown to Append and MergeAppend nodes.
>
> This patch eliminates the need to resort to subqueries to optimise UNIONs.
> It also enables more aggressive partition pruning.
> Not sure if it causes LIMIT pushdown to foreign partitions though.
>
> Applying this patch causes regressions in:
> - postgres_fdw tests
> - partitions tests
>
> This is due to subsequent partition pruning applied when LIMIT is pushed down - I guess that’s a (big) win.
>
> I would be happy to hear if the approach is sound.
>
> Thanks,
> Michal<limit-pushdown.patch>