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

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(at)taral(dot)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.

--
Taral <taral(at)taral(dot)net>
This message is digitally signed. Please PGP encrypt mail to me.
"Most parents have better things to do with their time than take care of
their children." -- Me