Re: Reject Foreign Tables from MIN/MAX indexscan Optimization?

"David E. Wheeler" <david(at)justatheory(dot)com> writes:
> In working on a foreign data wrapper, I noticed that a simple `MIN()` or `MAX()` query doesn’t push down, even though the remote server supports it and it will push down for a more complicated query.

Are you sure this is bad? I would hope that it'd only choose such
a plan if the remote server has a supporting index, else cost
considerations should cause us to reject it. Yeah, it looks a little
odd to ship the query in this form rather than as a MIN()/MAX(),
but I'm unconvinced that it's harmful per se. Maybe there is a
costing problem that needs to be fixed, but if so we should fix it
not lobotomize the planner.

> The attached patch fixes the issue by disabling the MIN/MAX
> indexscan optimization for foreign tables.

I don't like that too much. The original intention of
preprocess_minmax_aggregates was that it would only create indexscan
plans, which would automatically eliminate FDW relations. Some of
the comments still say that :-(, but in reality we got rid of that
restriction back in 9.1 (see 034967bdc and 8df08c848) because we
realized that the optimization could be applicable even when the
underlying plan isn't a simple indexscan. I think that the same
applies here, and so we might be throwing away useful cases.
In particular, your "let the remote side do the optimization"
argument is only valid for postgres_fdw; other FDWs might not
have equivalent smarts underneath them.

It would be good to look under the hood a little more and understand
why the MinMaxAgg path is winning, if it's not actually fast.

regards, tom lane