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

On Tue, 9 Jul 2019 at 16:56, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> (I've added Fujita-san and David Rowley to this discussion as they
> will have a better clue about some things I write below.)
>
> On Tue, Jul 9, 2019 at 9:12 AM Gert van Dijk <gertvdijk(at)gmail(dot)com> wrote:
> > First of all I want to thank Etsuro Fujita for implementing the exact
> > feature I was missing in
> > FDW 11.4, but now available in 12: pushing down of LIMIT & ORDER BY to
> > foreign tables
> > (commit d50d172e51). Now that I'm using PostgreSQL 12-beta2 from the
> > official Docker
> > image I noticed an omission that I wanted to report here which may be
> > relevant for those
> > like me, using FDW in a typical sharding setup.
> >
> > By querying purely foreign tables, I can confirm pushing down LIMIT &
> > ORDER BY is
> > working as expected on my installation.
> > However, when I use a typical sharding setup where the main table is
> > located on the FDW
> > node, with partitions of foreign tables, this seems not to activate
> > the new code path. I can
> > understand that pushing this down is not possible in cases where
> > *multiple* foreign tables
> > are to be scanned. However, it also does not work in the case where my
> > WHERE clause
> > condition causes to only connect to a *single* foreign table.
>
> 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.

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.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services