Understanding Execution Plans - Mailing list pgsql-general
| From | Oliver Weichhold |
|---|---|
| Subject | Understanding Execution Plans |
| Date | |
| Msg-id | a13da490903221205p480dada3xb9b70f297f75df5@mail.gmail.com Whole thread Raw |
| Responses |
Re: Understanding Execution Plans
|
| List | 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)
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 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?
pgsql-general by date: