Re: Why won't the query planner use my index?

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> On Wed, 2002-03-27 at 23:46, Robert Wille wrote:
>> test=# explain select max(id) from a;
>> This one is quite baffling. All the DB needs to do is look at the end of the primary key index.

> Postgres will never use an index for this kind of query. If you need
> this kind of functionality, perhaps you can manipulate the sequence
> directly, using nextval() or currval().

The traditional answer is

select id from a order by id desc limit 1;

which will give an indexscan-based plan in recent releases. Since this
is more functional than a max() query (because you can get at all the
columns of the row containing the maximum ID value, not only the max
itself), there's not been a huge amount of interest in teaching the
planner that there might be some relationship between btree indexes and
max/min aggregates. We do regularly get razzed by people who think that
such a relationship is "obvious" ... but I like Postgres' black-box
approach to aggregates, and am not eager to break it for little or no
gain in functionality.

Wille's test case does seem to expose some problems in current sources:
I notice that a plain "ANALYZE A" produces a ridiculously low reltuples
estimate. I think this might be because the update sequence in his
script ends up with the first pages of the table completely empty ---
that seems to be causing ANALYZE to do the wrong thing. Too tired to
look at it more tonight, though.

regards, tom lane