Re: Problems with order by, limit, and indices

Denis Perchine <dyp(at)perchine(dot)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