Understanding Execution Plans

Started by Oliver Weichholdabout 17 years ago2 messagesgeneral
Jump to latest
#1Oliver Weichhold
oliver@weichhold.com

I'm in the process of migrating a web application from a dedicated server to
VPS Hosting (Slicehost). During the test phase I've spotted a huge
performance advantage for the old dedicated server for some queries and I
need some help interpreting the execution plans.
Plan 1 - Dedicated Server Athlon 64 5000 - Debian 5.0 - 4GB Ram - 150 GB
off the shelf Sata HD
---

Limit (cost=16574.23..16574.28 rows=20 width=119) (actual
time=466.140..466.158 rows=3 loops=1)
-> Sort (cost=16574.23..16574.29 rows=24 width=119) (actual
time=466.135..466.141 rows=3 loops=1)
Sort Key: c.total_achievement_points
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=86.99..16573.68 rows=24 width=119)
(actual time=139.903..466.064 rows=3 loops=1)
-> Nested Loop Left Join (cost=86.99..16414.84 rows=24
width=108) (actual time=139.865..465.957 rows=3 loops=1)
Join Filter: (c.class_id = classes.id)
-> Nested Loop Left Join (cost=86.99..16385.44
rows=24 width=86) (actual time=139.846..465.773 rows=3 loops=1)
-> Nested Loop Left Join (cost=86.99..16186.44
rows=24 width=73) (actual time=139.826..448.932 rows=3 loops=1)
Join Filter: (c.race_id = races.id)
-> Nested Loop Left Join
(cost=86.99..16157.04 rows=24 width=60) (actual time=139.775..448.750
rows=3 loops=1)
Join Filter: (c.faction_id =
factions.id)
-> Bitmap Heap Scan on characters c
(cost=86.99..16128.72 rows=24 width=36) (actual time=139.721..448.574
rows=3 loops=1)
Recheck Cond: (realm_id = 227)
Filter:
((total_achievement_points > 0) AND (level = 80))
-> Bitmap Index Scan on
characters_realm_id (cost=0.00..86.98 rows=4597 width=0) (actual
time=26.076..26.076 rows=2028 loops=1)
Index Cond: (realm_id =
227)
-> Seq Scan on faction_categories
factions (cost=0.00..1.08 rows=8 width=28) (actual time=0.008..0.024 rows=8
loops=3)
-> Seq Scan on races (cost=0.00..1.10
rows=10 width=17) (actual time=0.004..0.025 rows=10 loops=3)
-> Index Scan using guilds_pkey on guilds g
(cost=0.00..8.28 rows=1 width=17) (actual time=5.598..5.599 rows=1 loops=3)
Index Cond: (c.guild_id = g.id)
-> Seq Scan on classes (cost=0.00..1.10 rows=10
width=26) (actual time=0.005..0.027 rows=10 loops=3)
-> Index Scan using realms_pkey on realms r
(cost=0.00..6.61 rows=1 width=15) (actual time=0.018..0.022 rows=1 loops=3)
Index Cond: ((r.id = 227) AND (c.realm_id = r.id))
Total runtime: 466.829 ms
(25 rows)

Plan 2 - Slicehost VPS 512 - Quadcore Opteron Xen VPS - Debian 5.0 - 512MB
RAM - Raid 10 Storage on Host
---

Limit (cost=17088.31..17088.36 rows=20 width=119) (actual
time=5620.050..5620.050 rows=3 loops=1)
-> Sort (cost=17088.31..17088.37 rows=24 width=119) (actual
time=5620.050..5620.050 rows=3 loops=1)
Sort Key: c.total_achievement_points
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=92.10..17087.76 rows=24 width=119)
(actual time=2016.018..5620.050 rows=3 loops=1)
-> Nested Loop Left Join (cost=92.10..16888.77 rows=24
width=106) (actual time=2016.018..5588.049 rows=3 loops=1)
-> Nested Loop Left Join (cost=92.10..16729.92
rows=24 width=95) (actual time=2016.018..5588.049 rows=3 loops=1)
Join Filter: (c.class_id = classes.id)
-> Nested Loop Left Join (cost=92.10..16700.52
rows=24 width=73) (actual time=2016.018..5588.049 rows=3 loops=1)
Join Filter: (c.race_id = races.id)
-> Nested Loop Left Join
(cost=92.10..16671.12 rows=24 width=60) (actual time=2016.018..5588.049
rows=3 loops=1)
Join Filter: (c.faction_id =
factions.id)
-> Bitmap Heap Scan on characters c
(cost=92.10..16642.80 rows=24 width=36) (actual time=2016.018..5588.049
rows=3 loops=1)
Recheck Cond: (realm_id = 227)
Filter:
((total_achievement_points > 0) AND (level = 80))
-> Bitmap Index Scan on
characters_realm_id (cost=0.00..92.09 rows=4743 width=0) (actual
time=76.001..76.001 rows=2033 loops=1)
Index Cond: (realm_id =
227)
-> Seq Scan on faction_categories
factions (cost=0.00..1.08 rows=8 width=28) (actual time=0.000..0.000 rows=8
loops=3)
-> Seq Scan on races (cost=0.00..1.10
rows=10 width=17) (actual time=0.000..0.000 rows=10 loops=3)
-> Seq Scan on classes (cost=0.00..1.10 rows=10
width=26) (actual time=0.000..0.000 rows=10 loops=3)
-> Index Scan using realms_pkey on realms r
(cost=0.00..6.61 rows=1 width=15) (actual time=0.000..0.000 rows=1 loops=3)
Index Cond: ((r.id = 227) AND (c.realm_id = r.id
))
-> Index Scan using guilds_pkey on guilds g
(cost=0.00..8.28 rows=1 width=17) (actual time=10.667..10.667 rows=1
loops=3)
Index Cond: (c.guild_id = g.id)
Total runtime: 5620.050 ms
(25 rows)

---
It seems that especially the joins take extremely long on the VPS versus the
dedicated machine but I'm not sure if that's caused by the the fact that the
dedicated machine has 8x the amount of RAM and thus can cache much more data
or because it has more I/O bandwidth due to the exclusive access to the
harddisk or a combination of both. Any suggestions?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Oliver Weichhold (#1)
Re: Understanding Execution Plans

Oliver Weichhold <oliver@weichhold.com> writes:

It seems that especially the joins take extremely long on the VPS versus the
dedicated machine but I'm not sure if that's caused by the the fact that the
dedicated machine has 8x the amount of RAM and thus can cache much more data
or because it has more I/O bandwidth due to the exclusive access to the
harddisk or a combination of both. Any suggestions?

I'd guess that your virtual machine is delivering seriously bad disk
access performance. The relative lack of RAM certainly isn't helping
though; if it had more then the kernel disk buffers could mask the poor
I/O to some extent.

You could perhaps fix the blame more accurately by doing some disk
benchmarking with bonnie or a similar tool.

regards, tom lane