Re: Seqscan in MAX(index_column)

Greg Stark <gsstark(at)mit(dot)edu> writes:
> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
>> Would it be possible to catch an unconstrained max(id)/min(id) and rewrite
>> it as "select id from table order by id [desc] limit1" on the fly in the
>> parser somewhere?
>> That would require fairly little code, and be transparent to the user.
>> I.e. low hanging fruit.

> What if there's no index on id? Then it would actually be slower than the
> straightforward approach. You would have to check both versions and take the
> one with the lowest cost, or check before rewriting for possible paths on that
> column.

If the fruit were all that low-hanging, it would've been done before
now, as I think this is all that people coming from other DBs expect.
But as Greg points out, it's not really a trivial planner change.

There are also semantic issues: how shall the planner decide which
aggregates are candidates for this treatment (I don't much care for
hardwiring some behavior to the names "max" and "min") and how shall
it decide which indexes match a given aggregate? In the presence of
multiple operator classes for a datatype, it's not obvious whether a
btree index has the same sort order that max/min need.

If you dig in the pghackers archives you can find some speculation about
extending aggregate definitions to associate max/min with appropriate
sort operators, but no one's done the legwork to make a concrete
proposal, let alone actually code it up.

regards, tom lane