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

On Jan 5, 2026, at 19:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

How does one tell it that there is a supporting index? I’m working with ClickHouse, where there are no indexes to speak of; this query relies on the inherent ordering of the columns.

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

It would be less harmful if the LIMIT pushed down; otherwise it’s pulling the entire table back into Postgres, no?

> Maybe there is a
> costing problem that needs to be fixed, but if so we should fix it
> not lobotomize the planner.

I would love to better understand how that all works, esp. WRT foreign tables. With an analytics database like ClickHouse, though, we’re trying to push down as much as possible.

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

But it needs the LIMIT, no?

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

Okay, but it’d be great to have a way to tell it “don’t do that” for those that do.

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

Where does one start?

D