On Tue, Mar 3, 2009 at 5:34 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
