Re: Problems with order by, limit, and indices

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


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.

            regards, tom lane