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@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------