- Mailing list pgsql-performance

I run the following command three times to prevent cache/disk results.

[...]
dps=> explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND
crc32=764518963 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1;
                                                          QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
  Limit  (cost=173.14..173.14 rows=1 width=4) (actual time=0.357..0.358
rows=1 loops=1)
    ->  Sort  (cost=173.14..173.22 rows=32 width=4) (actual
time=0.354..0.354 rows=1 loops=1)
          Sort Key: rec_id
          ->  Index Scan using url_crc on url  (cost=0.00..172.34
rows=32 width=4) (actual time=0.039..0.271 rows=50 loops=1)
                Index Cond: (crc32 = 764518963)
                Filter: ((crc32 <> 0) AND ((status = 200) OR (status =
304) OR (status = 206)))
  Total runtime: 0.410 ms
(7 rows)

dps=> explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND
crc32=764518963 AND status IN (200,304,206) ORDER BY crc32,rec_id LIMIT
1;
                                                          QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
  Limit  (cost=173.14..173.14 rows=1 width=8) (actual time=0.378..0.378
rows=1 loops=1)
    ->  Sort  (cost=173.14..173.22 rows=32 width=8) (actual
time=0.375..0.375 rows=1 loops=1)
          Sort Key: crc32, rec_id
          ->  Index Scan using url_crc on url  (cost=0.00..172.34
rows=32 width=8) (actual time=0.038..0.278 rows=50 loops=1)
                Index Cond: (crc32 = 764518963)
                Filter: ((crc32 <> 0) AND ((status = 200) OR (status =
304) OR (status = 206)))
  Total runtime: 0.432 ms
(7 rows)
dps=> explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND
crc32=419903683 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1;
                                                           QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
  Limit  (cost=0.00..37.03 rows=1 width=4) (actual time=156.712..156.713
rows=1 loops=1)
    ->  Index Scan using url_pkey on url  (cost=0.00..14996.82 rows=405
width=4) (actual time=156.707..156.707 rows=1 loops=1)
          Filter: ((crc32 <> 0) AND (crc32 = 419903683) AND ((status =
200) OR (status = 304) OR (status = 206)))
  Total runtime: 156.769 ms
(4 rows)

dps=> explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND
crc32=419903683 AND status IN (200,304,206) ORDER BY crc32,rec_id LIMIT
1;
                                                            QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------------
  Limit  (cost=1910.14..1910.14 rows=1 width=8) (actual
time=4.558..4.559 rows=1 loops=1)
    ->  Sort  (cost=1910.14..1911.15 rows=405 width=8) (actual
time=4.555..4.555 rows=1 loops=1)
          Sort Key: crc32, rec_id
          ->  Index Scan using url_crc on url  (cost=0.00..1892.60
rows=405 width=8) (actual time=0.042..2.935 rows=719 loops=1)
                Index Cond: (crc32 = 419903683)
                Filter: ((crc32 <> 0) AND ((status = 200) OR (status =
304) OR (status = 206)))
  Total runtime: 4.636 ms
(7 rows)

The value 764518963 is not common, it appears 50 times in the table.
The value 419903683 is the third most common value of the table url.

dps=> select u.crc32, count(*)  from url u group by u.crc32 order by
count(*) desc;
     crc32    | count
-------------+------
            0 | 82202
   -946427862 | 10545
    419903683 |   719
    945866756 |   670
[...]

How to setup pgsql to correctly select the good index for index scan ?

I run Pgsql 7.4.x
The database runs under pg_autovacuum daemon.
And a VACUUM FULL VERBOSE ANALYZE was done 10 hours before.

Cordialement,
Jean-Gérard Pailloncy