Problems with order by, limit, and indices

Hello,

DB: PostgreSQL 7.1 (snap-06012001)
OS: Linux

I have the following table:

slygreetings=> \d users
Table "users"
Attribute | Type | Modifier
-------------+-----------+------------------------------------------------
id | integer | not null default nextval('users_id_seq'::text)
sendername | text |
senderlname | text |
sendermail | text |
sender_ip | inet |
senderdate | timestamp |
rcptname | text |
rcptmail | text |
rcpt_ip | inet |
reason | text |
rcptdate | timestamp |
crypt | text |
active | boolean | default 'false'
variant_id | integer |
info | text |
Indices: users_crypt_key,
users_id_key,
users_rcptdate_vid_key

Vacuum ouput:
slygreetings=> vacuum verbose analyze users;
NOTICE: --Relation users--
NOTICE: Pages 23572: Changed 0, reaped 0, Empty 0, New 0; Tup 660510: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 156, MaxLen 2032; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 3.23s/0.26u sec.
NOTICE: Index users_id_key: Pages 1450; Tuples 660510. CPU 0.18s/0.80u sec.
NOTICE: Index users_crypt_key: Pages 3162; Tuples 660510. CPU 0.43s/0.82u
sec.
NOTICE: Index users_rcptdate_vid_key: Pages 2321; Tuples 660510. CPU
0.29s/0.76u sec.
NOTICE: --Relation pg_toast_18741--
NOTICE: Pages 8: Changed 0, reaped 0, Empty 0, New 0; Tup 35: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 647, MaxLen 2034; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_toast_18741_idx: Pages 2; Tuples 35. CPU 0.00s/0.00u sec.
NOTICE: Analyzing...
VACUUM

Index:

slygreetings=> \d users_rcptdate_vid_key
Index "users_rcptdate_vid_key"
Attribute | Type
------------+-----------
variant_id | integer
active | boolean
rcptdate | timestamp
btree

Statistics for variant_id (quite important, will see later)

slygreetings=> select count(variant_id),variant_id from users group by
variant_id;
count | variant_id
--------+------------
11 | 1
545067 | 2
20080 | 3
95351 | 4
1 | 5
(5 rows)

I need to get 60 last not active users for specific variant_id.

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)

EXPLAIN

Executor stats:

EXECUTOR STATISTICS
! system usage stats:
! 40.441787 elapsed 19.090000 user 6.340000 system sec
! [280.840000 user 85.980000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/201 [2290/2233] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 23572 read, 0 written, buffer hit rate =
0.00%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

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)

EXPLAIN

Executor stats:
EXECUTOR STATISTICS
! system usage stats:
! 0.057452 elapsed 0.000000 user 0.000000 system sec
! [280.840000 user 85.980000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [2290/2233] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 5 read, 0 written, buffer hit rate =
0.00%
! Local blocks: 0 read, 0 written, buffer hit rate =
0.00%
! Direct blocks: 0 read, 0 written

As far as you can see planner somehow ignores the possibility to use
users_rcptdate_vid_key for both where quals, and order by.

Can please someone help me with this? Is there any possibility to improve the
situation (or maybe I misunderstood something).

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------