Understanding Execution Plans

From: Oliver Weichhold <oliver(at)weichhold(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Understanding Execution Plans
Date: 2009-03-22 19:05:19
Message-ID: a13da490903221205p480dada3xb9b70f297f75df5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-03-22 19:19:01 Re: Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Previous Message Ben Ali Rachid 2009-03-22 17:52:25 Re: Srf function : missing library on PostgreSQL 8.3.6 on Windows?