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

On Wed, Jul 10, 2019 at 11:35 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>
> 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.

Thanks for all of your explanatory words on this. I also now see how
this is more a feature request, sorry about that. As I mentioned, I am
still quite new to PostgreSQL, and I thought I had identified a
'simple' omission in a recently merged feature for which I expected
too much. I now learned things are a bit more complex in that regard;
I wrongly assumed that FDW can be a query router for my case at this
point in time at least.

Would you like me to report it elsewhere now? And what would have been
the right place? -general, -performance, -hackers, ...?

> 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

Cool, thanks again!

Regards,

Gert