Re: Indexes on Aggregate Functions

Curt Sampson dijo:

> 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.

Because Postgres does not know what operator is running inside the
aggregate, so it doesn't know whether a given index can be used.
Aggregates are opaque functions. Maybe a hack could be written that
allows indexes to be used, allowing one to tie an operator to an
aggregate. I don't know if it's possible.

> 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

I think Postgres cannot do this because the index doesn't contain the
needed transaction visibility information; it's on the heap, so it has
to go there anyway.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Entristecido, Wutra
echa a Freyr a rodar
y a nosotros al mar" (cancion de Las Barreras)