Problems with order by, limit, and indices - Mailing list pgsql-general

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@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------