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)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
