I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1
machine, the index scans are being planned extremely low cost:
explain ANALYZE select * from email_entity where email_thread = 375629157;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59
rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1)
Index Cond: (email_thread = 375629157)
Total runtime: 0.207 ms
(3 rows)
But on the 8.3.3 machine, the index scans are being planned much higher cost:
explain ANALYZE select * from email_entity where email_thread = 375629157;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..2218.61
rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1)
Index Cond: (email_thread = 375629157)
Total runtime: 0.253 ms
(3 rows)
diffing the 'show all;' output reveals the following (left side is the low
cost plan, right side is the high cost plan server):
57c57
< effective_cache_size | 31800MB |
Sets the planner's assumption about the size of the disk cache.
---
effective_cache_size | 15300MB |
Sets the planner's assumption about the size of the disk cache.
72c72
< fsync | on |
Forces synchronization of updates to disk.
---
fsync | off |
Forces synchronization of updates to disk.
110c110
< log_line_prefix | |
Controls information prefixed to each log line.
---
log_line_prefix | user=%u,db=%d |
Controls information prefixed to each log line.
128,129c128,129
< max_fsm_pages | 2000000 |
Sets the maximum number of disk pages for which free space is tracked.
< max_fsm_relations | 1000 |
Sets the maximum number of tables and indexes for which free space is tracked.
---
max_fsm_pages | 4000000 |
Sets the maximum number of disk pages for which free space is tracked.
max_fsm_relations | 5000 |
Sets the maximum number of tables and indexes for which free space is tracked.
145,146c145,146
< server_version | 8.3.1 |
Shows the server version.
< server_version_num | 80301 |
Shows the server version as an integer.
---
server_version | 8.3.3 |
Shows the server version.
server_version_num | 80303 |
Shows the server version as an integer.
149c149
< shared_preload_libraries | |
Lists shared libraries to preload into server.
---
shared_preload_libraries | $libdir/plugins/plugin_debugger.so |
Lists shared libraries to preload into server.
Disabling the debugger had no effect on the slow server.
I then thought perhaps this was a difference between 8.3.1 and 8.3.3, so I
loaded the DB on a separate test machine and tried the query with both 8.3.1
and 8.3.3 on the same server:
engage=# show server_version;
server_version
----------------
8.3.1
(1 row)
explain ANALYZE select * from email_entity where email_thread = 375629157;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..1319.44
rows=1183 width=1046) (actual time=0.017..0.022 rows=4 loops=1)
Index Cond: (email_thread = 375629157)
Total runtime: 0.054 ms
(3 rows)
engage=# show server_version;
server_version
----------------
8.3.3
(1 row)
explain ANALYZE select * from email_entity where email_thread = 375629157;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..1319.44
rows=1183 width=1046) (actual time=0.018..0.022 rows=4 loops=1)
Index Cond: (email_thread = 375629157)
Total runtime: 0.055 ms
(3 rows)
As you might guess, the reason I started looking at this is that the high cost
changes the plan of a more complex query for the worse.
Any idea what might be influencing the plan on the other server? I tried
increasing the statistics target on the email_thread column and that helped to
a certain extent. Setting the statistics target to 1000 gets me a good enough
plan to help the complex query in question:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_email_entity_thread on email_entity (cost=0.00..26.36
rows=12 width=913) (actual time=0.028..0.040 rows=4 loops=1)
Index Cond: (email_thread = 375629157)
Total runtime: 0.092 ms
(3 rows)
But 26.36 is still not 4.59 like the other server estimates AND the statistics
target on that column is just the default 10 on the server with the 4.59 cost
estimate.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
