Re: Problems with order by, limit, and indices

Denis Perchine <dyp@perchine.com> writes:
> 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.)

> 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;

            regards, tom lane