Re: SELECT min(id) FROM test; uses seqscan

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> SELECT min(id) FROM test; uses sequential scan even if an index on "id"
>> exists. VACUUM ANALYZE doesn't help.

> This is a known stupidity of PG, and will probably be fixed in a relatively
> distant future release (when index entries are updated to match row
> status). The simple workaround is:

> Select id from test order by id as limit 1;

Keeping status markers in index entries really doesn't have much of
anything to do with it. The hard part is teaching the planner to
generate a completely different kind of plan for some aggregates
(viz min/max) than it does for others --- but only when an index of the
right type is available. While it might not be too bad in DBMSes that
have a small, fixed set of aggregate functions, PG's extensible set
of aggregates and datatypes (not to mention index types) makes this
rather difficult. We need to design some sort of tabular representation
of when and how to generate a specialized plan.

regards, tom lane