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 (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
