Hi list,
once again I do not understand how the query planner works and why it
apparently does not find the best result.
I have a table with about 125 million rows. There is a char(5) column
with a (non-unique) index. When I try to find the distinct values in
this column using the following sql statement:
select distinct exchange from foo
the query planner chooses not to use the index, but performs a
sequential scan. When I disfavour the use of sequential scans ("set
enable_seqscan = off") the performance is more than 6 times better. Why
does the query planner's plan go wrong? The table has been vacuum
analyzed just before I ran the queries.
Here is the plan when I let the query planner alone:
QUERY PLAN
--------------------------------------------------------------------------------
Unique (cost=23057876.40..23683350.48 rows=4 width=9)
-> Sort (cost=23057876.40..23370613.44 rows=125094816 width=9)
Sort Key: exchange
-> Seq Scan on quotes (cost=0.00..3301683.16 rows=125094816
width=9)
(4 rows)
This is what really happens:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=23057876.40..23683350.48 rows=4 width=9) (actual
time=1577159.744..1968911.024 rows=4 loops=1)
-> Sort (cost=23057876.40..23370613.44 rows=125094816 width=9)
(actual time=1577159.742..1927400.118 rows=125094818 loops=1)
Sort Key: exchange
-> Seq Scan on quotes (cost=0.00..3301683.16 rows=125094816
width=9) (actual time=0.022..169744.162 rows=125094818 loops=1)
Total runtime: 1969844.753 ms
(5 rows)
With "enable_seqscan = off" I get this plan:
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Unique (cost=0.00..89811549.81 rows=4 width=9)
-> Index Scan using quotes_exchange_key on quotes
(cost=0.00..89498812.77 rows=125094816 width=9)
(2 rows)
And again with execution times:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..89811549.81 rows=4 width=9) (actual
time=0.079..313068.922 rows=4 loops=1)
-> Index Scan using quotes_exchange_key on quotes
(cost=0.00..89498812.77 rows=125094816 width=9) (actual
time=0.078..273787.493 rows=125094818 loops=1)
Total runtime: 313068.967 ms
(3 rows)
I understand that from looking at the estimations (89811549.81 with
index scan vs. 23683350.48 with sequential scan) the query planner had
to choose the sequential scan. So maybe I have to tune the planner cost
constants? Indeed I did some changes to these values, but in my opinion
this should make index scans preferable:
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
random_page_cost = 1.0
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
effective_cache_size = 4GB
The machine is a dedicated database server with two dual-core xeon
processors and 8 GB memory.
Thanks for your help,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
