Index problems - Mailing list pgsql-admin

Hi,

When trying to explain a developer of our organisation the reson why the index
was'nt used, i was confused my self.

The simple question is: Why does the analyzer only use the index when the
table has a few differend values ?

As you can see, when de seqscan is off, the query is mutch faster. It does'nt
matter how big the table is, i've tried this with 100 times more, the result
is the same.

I've tried this on version 7.3.2 and 7.3.4.

Regards,
Ferdinand Smit

==========

test=# SELECT r, count(r) FROM test GROUP BY r;
 r  | count
----+-------
  0 |  1344
  1 |   288
  2 |   440
  3 |  1096
  4 |   256
  5 |   696
  6 |  2048
  7 |   512
  8 |  6424
  9 |   128
 10 |   976
 11 |    16
 12 |  1488
 13 |    80
 14 |   136
 15 |   112
 16 |    56
 17 |    56
 18 |    16
 19 |   184
(20 rows)

test=# VACUUM FULL ANALYZE test;
VACUUM
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE r = 13;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..277.40 rows=207 width=4) (actual
time=0.05..24.19 rows=80 loops=1)
   Filter: (r = 13)
 Total runtime: 24.31 msec
(3 rows)

test=# SET enable_seqscan = off;
SET
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE r = 13;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using key_regio2 on test  (cost=0.00..285.83 rows=207 width=4)
(actual time=0.30..0.73 rows=80 loops=1)
   Index Cond: (r = 13)
 Total runtime: 0.87 msec
(3 rows)

test=# SET enable_seqscan = on;
SET
test=# DELETE FROM test WHERE r > 10;
DELETE 2144
test=# VACUUM FULL ANALYZE test;
VACUUM
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE r = 13;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..240.60 rows=638 width=4) (actual
time=20.87..20.87 rows=0 loops=1)
   Filter: (r = 13)
 Total runtime: 20.93 msec
(3 rows)

test=# DELETE FROM test WHERE r > 5;
DELETE 10088
test=# VACUUM FULL ANALYZE test;
VACUUM
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE r = 13;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Index Scan using key_regio2 on test  (cost=0.00..5.69 rows=1 width=4) (actual
time=0.25..0.25 rows=0 loops=1)
   Index Cond: (r = 13)
 Total runtime: 0.33 msec
(3 rows)