Thread: Index problems

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)

On Wed, 3 Dec 2003, Ferdinand Smit wrote:

> 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

There are a fiew things going on:

First, the statistics are overestimating the number of matching rows (by
say a factor of 3 in the first query).  You may wish to increase the
statistics target (alter table test alter column r set statistics <n>)
for something greater than 10, try 20 or 100 and re-analyze the table and
see if that lowers the estimated costs for the index scan.

Second, it's also possible that on your system random_page_cost should be
lower than 4.  Lowering that value lowers the estimated cost for index
scans.

Finally, it's also possible that the table is reasonably grouped by values
of r but that the statistics aren't realizing that fact. What does the row
in pg_statistic for that column show?