pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex scan cost
5+ messages / 3 participants
[nested] [flat]
* index scan cost
@ 2008-07-17 21:21 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jeff Frost @ 2008-07-17 21:21 UTC (permalink / raw)
To: pgsql-performance
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@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index scan cost
@ 2008-07-17 22:58 Dennis Brakhane <brakhane@googlemail.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 5+ messages in thread
From: Dennis Brakhane @ 2008-07-17 22:58 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-performance
The "fast" server makes a much more accurate estimation of the number
of rows to expect (4 rows are returning, 1 was estimated). The "slow"
server estimates 1151 rows. Try running ANALYZE on the slow one
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index scan cost
@ 2008-07-17 23:12 Jeff Frost <jeff@frostconsultingllc.com>
parent: Dennis Brakhane <brakhane@googlemail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Frost @ 2008-07-17 23:12 UTC (permalink / raw)
To: Dennis Brakhane <brakhane@googlemail.com>; +Cc: pgsql-performance
On Fri, 18 Jul 2008, Dennis Brakhane wrote:
> The "fast" server makes a much more accurate estimation of the number
> of rows to expect (4 rows are returning, 1 was estimated). The "slow"
> server estimates 1151 rows. Try running ANALYZE on the slow one
You're quite right. I probably didn't mention that the slow one has been
analyzed several times. In fact, every time adjusted the statistics target
for that column I analyzed, thus the eventually better, but still inaccurate
estimates toward the bottom of the post.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index scan cost
@ 2008-07-18 04:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-07-18 04:37 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-performance
Jeff Frost <jeff@frostconsultingllc.com> writes:
> 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:
> 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)
> 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)
This isn't a "cost" problem, this is a "stats" problem. Why does the
second server think 1151 rows will be returned? Try comparing the
pg_stats entries for the email_thread column on both servers ... seems
like they must be significantly different.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index scan cost
@ 2008-08-09 06:17 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Frost @ 2008-08-09 06:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Jeff Frost <jeff@frostconsultingllc.com> writes:
>
>> 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:
>>
>
>
>> 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)
>>
>
>
>> 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)
>>
>
> This isn't a "cost" problem, this is a "stats" problem. Why does the
> second server think 1151 rows will be returned? Try comparing the
> pg_stats entries for the email_thread column on both servers ... seems
> like they must be significantly different.
>
Sorry it took me a while to close the loop on this. So, the server that
had the less desirable plan had actually been analyzed more recently by
autovacuum. When I went back to compare the stats on the faster server,
autovacuum had analyzed it and the plan was now more similar. Adjusting
the stats target up for that column helped on both servers though it
never did get back as close as before.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-08-09 06:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-17 21:21 index scan cost Jeff Frost <jeff@frostconsultingllc.com>
2008-07-17 22:58 ` Dennis Brakhane <brakhane@googlemail.com>
2008-07-17 23:12 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-07-18 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-09 06:17 ` Jeff Frost <jeff@frostconsultingllc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox