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

On Wed, Jul 10, 2019 at 5:48 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Wed, Jul 10, 2019 at 9:38 AM David Rowley
> <david.rowley@2ndquadrant.com> wrote:
> > 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.

I think so too.

> 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.

Yeah, that's really what I'm thinking: in other words, partitionwise
limit restriction.  I'd like to work on it for PG13 if I have time.

Best regards,
Etsuro Fujita

PS: in the StackExchange site, Gert van Dijk stated this:

Running ANALYZE against the foreign table (on FDW instance). Takes a
huge amount of time; looks like it's full tablescanning the remote
table?

Yeah, that's right; when analyzing a remote table, postgres_fdw
retrieves all rows form the remote table, which would take a long time
if the remote table is large.  So I'm planning to work on this issue
for PG13.

[1]
https://dba.stackexchange.com/questions/242358/how-do-i-get-postgresql-fdw-to-push-down-the-limit-to-the-single-backend-serve