Re: FDW does not push down LIMIT & ORDER BY with sharding (partitions)

On Wed, Jul 10, 2019 at 9:38 AM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On Tue, 9 Jul 2019 at 16:56, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > As far as I can tell, LIMIT cannot be pushed below an Append or
> > MergeAppend that's used to combine the outputs of individual
> > partitions, which if I read correctly, you already know. It's true
> > that there's no Append/MergeAppend node in the *final* plan of your
> > example query, because there's only partition to be scanned after
> > pruning, but the Append/MergeAppend node remains in the plan through
> > the planning stage where LIMIT is added to the plan and only removed
> > in the final stage of planning. The final stage that removes the
> > Append/MergeAppend doesn't reassess whether the LIMIT on top (if any)
> > should be applied to the partition directly, which means the
> > partition's FDW never gets to see the LIMIT.
>
> I'm not so sure it's true that it's not possible to push the LIMIT
> below an Append/MergeAppend node. It seems perfectly fine to me,
> However, if there is more than 1 subnode to the Append/MergeAppend,
> then we'd need to keep the top-level LIMIT in place to ensure we don't
> output too many rows.

Yeah, I really meant to say that Postgres *currently doesn't* push
LIMIT under Append/MergeAppend.

> In any case, this is not a bug, so we really shouldn't discuss on
> -bugs. It just seems like a limitation of d50d172e51 to me. The
> setrefs.c code added in 8edd0e79 always gets rid of the
> Append/MergeAppend when there's just 1 subnode, so it does not seem
> that unreasonable that planner code that's called before that could
> assume that such an Append/MergeAppend path would not make it into the
> final plan. It could do whatever work that it needs to on the single
> subpath instead.

I see. Agree that this is not a bug of either of the commits I mentioned.

However, rather than calling this a limitation of d50d172e51, which
IIUC was an FDW-specific effort, I'd say that we lack the feature to
push LIMIT under Append/MergeAppend. If we had that feature, then
much like in the case of grouping that can be pushed under
Append/MergeAppend (at least for partitioned tables), we wouldn't need
do anything special for the single-child cases.

Thanks,
Amit