Re: No index maximum? (was Re: No merge sort?)

On Mon, Mar 17, 2003 at 11:23:47 -0600, Taral <taral@taral.net> wrote:
> On Sat, Mar 15, 2003 at 09:23:28AM -0600, Bruno Wolff III wrote:
> > On Fri, Mar 14, 2003 at 14:19:46 -0600,
> >   Taral <taral@taral.net> wrote:
> > > Same setup, different query:
> > > 
> > > test=> explain select max(time) from test where id = '1';
> > > NOTICE:  QUERY PLAN:
> > > 
> > > Aggregate  (cost=5084.67..5084.67 rows=1 width=0)
> > >   ->  Index Scan using idx on test  (cost=0.00..5081.33 rows=1333 width=0)
> > > 
> > > Since the index is (id, time), why isn't the index being used to
> > > retrieve the maximum value?
> > 
> > It looks like an index scan is being done.
> > 
> > If the index was on (time, id) instead of (id, time), then you could get
> > a further speed up by rewriting the query as:
> > select time from test where id = '1' order by time desc limit 1;
> 
> Yes, that's exactly it. It's an index _scan_. It should simply be able
> to read the maximum straight from the btree.

max and min don't use indexes. They are generic aggregate functions and
postgres doesn't have the special knowledge to know that for those
aggregate functions and index can be used. You can get around this
by rewriting the query as I previously indicated.

For more details on why things are this way, search the archives. This
topic comes up a lot.

I was also mistaken about have to switch the index around for this case.
It should work the way you have it (if you rewrite the query).