Re: Problems with order by, limit, and indices

> > Example with variant_id = 2
> >
> > slygreetings=> explain select * from users where variant_id=2 AND
> > active='f' order by rcptdate limit 60;
> > NOTICE:  QUERY PLAN:
> >
> > Limit  (cost=77117.18..77117.18 rows=60 width=145)
> >   -> Sort  (cost=77117.18..77117.18 rows=162640 width=145)
> >     -> Seq Scan on users  (cost=0.00..33479.65 rows=162640 width=145)
>
> This plan looks fine to me, considering that variant_id=2 is the vast
> majority of the table.  An indexscan will be slower, except perhaps if
> you've recently CLUSTERed the table on this index.  (If you don't
> believe me, try it with ENABLE_SEQSCAN set to OFF.)

I would agree with you if there was no limit specified. As far as I can
understand it is possible to traverse users_rcptdate_vid_key Forward,
and get 60 tuples, than finish. And that tuples will be already sorted (index
includes rcptdate also).

> > Example with variant_id = 5
> >
> > slygreetings=> explain select * from users where variant_id=5 AND
> > active='f' order by rcptdate 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)
>
> You could probably get a plan without the sort step if you said
>     ... order by variant_id, rcptdate;

No way, it just get all tuples for the qual, sort them, and the limiting.
That's horrible...

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)

EXPLAIN
slygreetings=> explain select * from users where variant_id=5 AND active='f'
order by rcptdate,variant_id,active 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)

EXPLAIN

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------