Indexes on Aggregate Functions

On 28 Jun 2002, Jason Earl wrote:

> SELECT setval('test_s_seq', (SELECT max(s) + 1 FROM test));
>
> PostgreSQL doesn't use the indexes on aggregate functions (like max())
> so it would be faster on large tables to write that as:
>
> SELECT setval('test_s_seq', (SELECT s + 1 FROM test ORDER BY s DESC
> LIMIT 1));

I've wondered about this, actually. Why doesn't postgres use the
indexes? For something like MAX(s) it would certainly be a lot faster.

Another cool optimisation that MS SQL Server does is, if the
information requested is looked up in an index, and all the columns
you're retrieving are already in the index data, it doesn't bother
retrieving the values from the table itself (though presumably it
checks for triggers to execute and so on). E.g., if you have

    CREATE TABLE foo (
    mykey        int PRIMARY KEY,
    otherkey_part1     int NOT NULL,
    otherkey_part2    varchar(255) NOT NULL,
    morestuff    varchar(255) NOT NULL);
    CREATE INDEX otherkeys ON foo (otherkey_part1, otherkey_part2);
    SELECT otherkey_part1, otherkey_part2 FROM foo
    WHERE otherkey_part1 = 17;

that query will read only the index, not the table itself.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC