Re: Indexes on Aggregate Functions

> -----Original Message-----
> From: Curt Sampson [mailto:cjs(at)cynic(dot)net]
> Sent: 01 July 2002 07:18
> To: Jason Earl
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 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.

<whine>mysql does this</whine>
Can't it be a special behaviour OF the aggregate function;
if the function implementation could observe that there were indexes
associated with the data it had to operate against, it might select the
right index and optimise.

This would perhaps involve having an extra advanced interface which
aggregate functions could optionally support, to get a look-in earlier on in
the process.

[I'm talking through my hat here]

Sam