Comparisons on
numerics aren't terribly fast though (in either release). I wonder
whether you could change the key columns to int or bigint.
I changed the affected columns from numeric to integers and I was unable to get any performance gain:
8.3.3: 1195 ms
8.2.12: 611 ms
I've attached the new query plans.
Are you doing
something to force the join order, like running with a small
join_collapse_limit setting? If so maybe you shouldn't.
No, we left the join_collapse_limit to the default 8. We tried a higher value, but there was no difference in performance.
I'll post the query and the table descriptions in separate messages to the list to avoid my mail from being rejected for exceeding the size limit :)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.061..611.061 rows=0 loops=1)
-> Sort (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.053..611.053 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=955.90..6986.00 rows=1 width=20) (actual time=610.942..610.942 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=955.90..4989.27 rows=523 width=76) (actual time=28.274..548.664 rows=1104 loops=1)
-> Hash Join (cost=955.90..3195.56 rows=526 width=52) (actual time=28.123..520.620 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..2002.69 rows=61789 width=16) (actual time=0.026..253.360 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=954.15..954.15 rows=140 width=44) (actual time=25.795..25.795 rows=332 loops=1)
-> Nested Loop (cost=0.00..954.15 rows=140 width=44) (actual time=0.546..24.364 rows=332 loops=1)
-> Nested Loop (cost=0.00..562.62 rows=100 width=40) (actual time=0.471..15.759 rows=336 loops=1)
-> Nested Loop (cost=0.00..74.40 rows=156 width=28) (actual time=0.233..6.404 rows=376 loops=1)
-> Nested Loop (cost=0.00..45.04 rows=4 width=20) (actual time=0.157..0.324 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..7.30 rows=6 width=12) (actual time=0.076..0.108 rows=7 loops=1)
Index Cond: (display_unit_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.016..0.018 rows=1 loops=7)
Index Cond: (t2.day_part_id = t10.id)
Filter: (active <> 0)
-> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.09 rows=100 width=16) (actual time=0.030..0.817 rows=94 loops=4)
Index Cond: (t2.id = t11.skin_id)
Filter: (active <> 0)
-> Index Scan using loop_slot_pkey on loop_slot t3 (cost=0.00..3.12 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.90 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t3.id = t7.owner_resource_id)
Filter: (active <> 0)
-> Index Scan using idx_bundle_content_bundle_id on bundle_content t8 (cost=0.00..3.40 rows=1 width=28) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9 (cost=0.00..3.77 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 611.680 ms
(35 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6891.89..6891.90 rows=1 width=20) (actual time=1194.811..1194.811 rows=0 loops=1)
-> Sort (cost=6891.89..6891.89 rows=1 width=20) (actual time=1194.803..1194.803 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3108.63..6891.88 rows=1 width=20) (actual time=1194.733..1194.733 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)))
-> Hash Join (cost=3108.63..4816.57 rows=571 width=76) (actual time=799.114..1135.524 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8 (cost=0.00..1441.17 rows=69617 width=28) (actual time=0.042..263.815 rows=69617 loops=1)
-> Hash (cost=3101.44..3101.44 rows=575 width=52) (actual time=593.227..593.227 rows=1104 loops=1)
-> Hash Join (cost=975.62..3101.44 rows=575 width=52) (actual time=98.739..588.389 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..1891.69 rows=60901 width=16) (actual time=0.035..249.993 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=973.66..973.66 rows=157 width=44) (actual time=96.404..96.404 rows=332 loops=1)
-> Nested Loop (cost=84.27..973.66 rows=157 width=44) (actual time=23.020..94.924 rows=332 loops=1)
-> Hash Join (cost=84.27..539.32 rows=120 width=40) (actual time=22.881..86.225 rows=336 loops=1)
Hash Cond: (t3.id = t11.slot_id)
-> Seq Scan on loop_slot t3 (cost=0.00..362.56 rows=9129 width=12) (actual time=0.052..38.501 rows=8951 loops=1)
Filter: (active <> 0)
-> Hash (cost=81.97..81.97 rows=184 width=28) (actual time=13.379..13.379 rows=376 loops=1)
-> Nested Loop (cost=0.00..81.97 rows=184 width=28) (actual time=3.188..11.716 rows=376 loops=1)
-> Nested Loop (cost=0.00..51.98 rows=4 width=20) (actual time=0.309..0.499 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..7.96 rows=7 width=12) (actual time=0.181..0.215 rows=7 loops=1)
Index Cond: (display_unit_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.024..0.027 rows=1 loops=7)
Index Cond: (t2.day_part_id = t10.id)
Filter: (t2.active <> 0)
-> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.16 rows=107 width=16) (actual time=0.729..2.100 rows=94 loops=4)
Index Cond: (t11.skin_id = t2.id)
Filter: (t11.active <> 0)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.61 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0)
-> Index Scan using content_pkey on content t9 (cost=0.00..3.58 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t9.id = t8.content_id)
Total runtime: 1195.615 ms
(38 rows)
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
