Search Postgresql Archives

How does the query planner make its plan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux