Re: Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1

felix(at)crowfix(dot)com writes:

> However, in the process of investigating this, my boss found something
> which we do not understand. A table with a primary key 'id' takes 200
> seconds to SELECT MAX(id), but is as close to instantaneous as you'd
> want for SELECT ID ORDER BY ID DESC LIMIT 1. I understand why
> count(*) has to traverse all records, but why does MAX have to? This
> table has about 750,000 rows, rather puny.

As I understand it, because aggregates in PG are extensible (the query
planner just knows it's calling some function), MAX isn't specially
handled--the planner doesn't know it's equivalent to the other query.

There has been some talk of special-casing this, but I'm not sure
where it lead--you might check the archives.

-Doug