Hi,
We are currently running postgres 8.2 and are evaluating the upgrade to 8.3.
Some of our tests are indicating that postgresql 8.3 is actually degrading
the
performance of some of our queries by a factor of 10 or more. The queries
in
question are selects that are heavy on joins (~10 tables) with a lot of
timestamp-based conditions in where clauses. The tables and queries are
tuned,
that is, there is no issue with the table structure, or missing indexes.
This
is a side-by-side query performance measurement between 8.2 and 8.3 with an
identical dataset and schema.
8.2.12 8.3.3
Time (ms) Time (ms)
1st 2nd 1st 2nd
time time time time
Query 1 759 130 3294 1758
attached you will find the explain analyze for this query. Any insight into
this issue would be very appreciated. Thanks.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=8251.47..8251.48 rows=1 width=37) (actual
time=3294.104..3294.104 rows=0 loops=1)
-> Sort (cost=8251.47..8251.48 rows=1 width=37) (actual
time=3294.096..3294.096 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Hash Join (cost=5509.94..8251.46 rows=1 width=37) (actual
time=3294.030..3294.030 rows=0 loops=1)
Hash Cond: (t9.id = t8.content_id)
Join Filter: ((t2.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t6.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t8.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t10.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text,
'YYYY/MM/DD HH:MI:SS'::text)))
-> Seq Scan on content t9 (cost=0.00..2272.26 rows=68026
width=16) (actual time=3.891..377.945 rows=68045 loops=1)
-> Hash (cost=5498.91..5498.91 rows=882 width=93) (actual
time=2625.501..2625.501 rows=1104 loops=1)
-> Hash Join (cost=3590.26..5498.91 rows=882 width=93)
(actual time=2241.886..2620.141 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8
(cost=0.00..1638.89 rows=69589 width=45) (actual time=5.160..334.263 rows=69606
loops=1)
-> Hash (cost=3579.17..3579.17 rows=887 width=56)
(actual time=1998.681..1998.681 rows=1104 loops=1)
-> Hash Join (cost=1317.36..3579.17 rows=887
width=56) (actual time=1367.063..1993.592 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6
(cost=0.00..2023.34 rows=61227 width=24) (actual time=3.785..390.578 rows=60919
loops=1)
Filter: (active <> 0::numeric)
-> Hash (cost=1314.41..1314.41
rows=236 width=48) (actual time=1355.121..1355.121 rows=332 loops=1)
-> Nested Loop
(cost=490.86..1314.41 rows=236 width=48) (actual time=256.077..1353.495
rows=332 loops=1)
-> Hash Join
(cost=490.86..628.39 rows=181 width=49) (actual time=240.596..1274.582 rows=336
loops=1)
Hash Cond:
(t11.slot_id = t3.id)
-> Nested Loop
(cost=0.00..131.76 rows=288 width=33) (actual time=92.007..1149.886 rows=376
loops=1)
-> Nested Loop
(cost=0.00..77.95 rows=7 width=24) (actual time=41.217..41.425 rows=4 loops=1)
-> Index
Scan using idx_day_part_du on day_part t10 (cost=0.00..8.73 rows=11 width=17)
(actual time=23.893..23.926 rows=7 loops=1)
Index Cond: (display_unit_id = 250893::numeric)
Filter: (active <> 0::numeric)
-> Index
Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=24)
(actual time=2.484..2.486 rows=1 loops=7)
Index Cond: (t2.day_part_id = t10.id)
Filter: (t2.active <> 0::numeric)
-> Index Scan
using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.54 rows=92
width=25) (actual time=12.726..276.412 rows=94 loops=4)
Index
Cond: (t11.skin_id = t2.id)
Filter:
(t11.active <> 0::numeric)
-> Hash
(cost=380.45..380.45 rows=8833 width=16) (actual time=121.457..121.457
rows=8950 loops=1)
-> Seq Scan on
loop_slot t3 (cost=0.00..380.45 rows=8833 width=16) (actual time=5.744..84.111
rows=8950 loops=1)
Filter:
(active <> 0::numeric)
-> Index Scan using
idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.78 rows=1
width=24) (actual time=0.216..0.220 rows=1 loops=336)
Index Cond:
(t7.owner_resource_id = t3.id)
Filter: (t7.active <>
0::numeric)
Total runtime: 3294.712 ms
(39 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=10675.55..10675.56 rows=2 width=52) (actual
time=758.259..758.259 rows=0 loops=1)
-> Sort (cost=10675.55..10675.55 rows=2 width=52) (actual
time=758.250..758.250 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=0.00..10675.54 rows=2 width=52) (actual
time=758.055..758.055 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t6.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t8.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD
HH:MI:SS'::text)) OR (t10.not_modified_since >=
to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR
(t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text,
'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=0.00..7966.45 rows=698 width=108) (actual
time=113.843..458.416 rows=1104 loops=1)
-> Nested Loop (cost=0.00..5463.73 rows=703 width=59)
(actual time=92.699..398.968 rows=1104 loops=1)
-> Nested Loop (cost=0.00..1287.57 rows=186
width=51) (actual time=39.570..128.132 rows=332 loops=1)
-> Nested Loop (cost=0.00..755.75 rows=135
width=54) (actual time=39.448..118.909 rows=336 loops=1)
-> Nested Loop (cost=0.00..98.83
rows=211 width=35) (actual time=19.050..27.403 rows=376 loops=1)
-> Nested Loop (cost=0.00..58.75
rows=5 width=27) (actual time=18.892..19.081 rows=4 loops=1)
-> Index Scan using
idx_day_part_du on day_part t10 (cost=0.00..8.41 rows=8 width=20) (actual
time=18.739..18.775 rows=7 loops=1)
Index Cond:
(display_unit_id = 250893::numeric)
Filter: (active <>
0::numeric)
-> Index Scan using
idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=30) (actual
time=0.028..0.031 rows=1 loops=7)
Index Cond:
(t2.day_part_id = t10.id)
Filter: (active <>
0::numeric)
-> Index Scan using
idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.85 rows=93 width=30)
(actual time=0.053..1.382 rows=94 loops=4)
Index Cond: (t2.id =
t11.skin_id)
Filter: (active <>
0::numeric)
-> Index Scan using loop_slot_pkey on
loop_slot t3 (cost=0.00..3.10 rows=1 width=19) (actual time=0.226..0.229
rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0::numeric)
-> Index Scan using
idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.93 rows=1
width=30) (actual time=0.009..0.013 rows=1 loops=336)
Index Cond: (t3.id =
t7.owner_resource_id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_schedule_id on
bundle t6 (cost=0.00..22.09 rows=29 width=30) (actual time=0.694..0.784 rows=3
loops=332)
Index Cond: (t6.schedule_id = t7.id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_content_bundle_id on
bundle_content t8 (cost=0.00..3.55 rows=1 width=60) (actual time=0.034..0.039
rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9
(cost=0.00..3.83 rows=1 width=19) (actual time=0.227..0.231 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 759.001 ms
(34 rows)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
