Re: Problems with order by, limit, and indices

> > slygreetings=> explain select * from users where variant_id=5 AND
> > active='f' order by rcptdate,variant_id limit 60;
> > NOTICE: QUERY PLAN:
> >
> > Limit (cost=13005.10..13005.10 rows=60 width=145)
> > -> Sort (cost=13005.10..13005.10 rows=3445 width=145)
> > -> Index Scan using users_rcptdate_vid_key on users
> > (cost=0.00..12658.35 rows=3445 width=145)
>
> Now, that's not what I told you to do, is it? It works fine for me:
>
> regression=# create table users (variant_id int , active bool, rcptdate
> date); CREATE
> regression=# create index usersind on users( variant_id,rcptdate,active);
> CREATE
> regression=# explain select * from users where variant_id=5 AND active='f'
> regression-# order by rcptdate limit 1;
> NOTICE: QUERY PLAN:
>
> Limit (cost=8.22..8.22 rows=1 width=9)
> -> Sort (cost=8.22..8.22 rows=5 width=9)
> -> Index Scan using usersind on users (cost=0.00..8.16 rows=5
> width=9)
>
> EXPLAIN
> regression=# explain select * from users where variant_id=5 AND active='f'
> regression-# order by variant_id,rcptdate limit 1;
> NOTICE: QUERY PLAN:
>
> Limit (cost=0.00..1.63 rows=1 width=9)
> -> Index Scan using usersind on users (cost=0.00..8.16 rows=5 width=9)
>
> EXPLAIN
>
> The specified sort order has to match the index if you hope to avoid
> a sort step.

Dummy me... My aplogies for the time I stole...

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------