On Tue, Mar 3, 2009 at 12:38 PM, <[email protected]> wrote:
> if you haven't done a vaccum analyse on either installation then postgres'
> idea of what sort of data is in the database is unpredictable, and as a
> result it's not surprising that the two systems guess differently about what
> sort of plan is going to be most efficiant.
>
> try doing vaccum analyse on both databases and see what the results are.
>
> David Lang
>
These are the results with vacuum analyze:
8.2.12: 624.366 ms
8.3.3: 1273.601 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=9832.91..9832.92 rows=2 width=52) (actual time=623.808..623.808
rows=0 loops=1)
-> Sort (cost=9832.91..9832.92 rows=2 width=52) (actual
time=623.799..623.799 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=0.00..9832.90 rows=2 width=52) (actual
time=623.467..623.467 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..7381.49 rows=598 width=108) (actual
time=75.634..556.642 rows=1104 loops=1)
-> Nested Loop (cost=0.00..5087.76 rows=602 width=59)
(actual time=71.744..523.690 rows=1104 loops=1)
-> Nested Loop (cost=0.00..1171.66 rows=165
width=51) (actual time=66.427..499.798 rows=332 loops=1)
-> Nested Loop (cost=0.00..684.77 rows=117
width=54) (actual time=38.266..440.024 rows=336 loops=1)
-> Nested Loop (cost=0.00..84.08
rows=182 width=35) (actual time=16.785..402.046 rows=376 loops=1)
-> Nested Loop (cost=0.00..52.33
rows=4 width=27) (actual time=11.355..11.547 rows=4 loops=1)
-> Index Scan using
idx_day_part_du on day_part t10 (cost=0.00..8.28 rows=7 width=19) (actual
time=0.713..0.748 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=1.526..1.529 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.81 rows=90 width=30)
(actual time=1.370..96.925 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.29 rows=1 width=19) (actual time=0.083..0.087
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..4.15 rows=1
width=30) (actual time=0.154..0.163 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..23.37 rows=29 width=30) (actual time=0.025..0.040 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.80 rows=1 width=60) (actual time=0.011..0.015
rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9
(cost=0.00..4.05 rows=1 width=19) (actual time=0.013..0.017 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 624.366 ms
(34 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=7384.05..7384.06 rows=1 width=37) (actual
time=1273.060..1273.060 rows=0 loops=1)
-> Sort (cost=7384.05..7384.06 rows=1 width=37) (actual
time=1273.052..1273.052 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3303.53..7384.04 rows=1 width=37) (actual
time=1272.976..1272.976 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=3303.53..5208.77 rows=515 width=93) (actual
time=863.416..1209.038 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8 (cost=0.00..1639.06
rows=69606 width=45) (actual time=0.077..272.391 rows=69606 loops=1)
-> Hash (cost=3297.06..3297.06 rows=518 width=56)
(actual time=651.968..651.968 rows=1104 loops=1)
-> Hash Join (cost=1040.33..3297.06 rows=518
width=56) (actual time=127.291..646.867 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..2023.55
rows=60798 width=24) (actual time=0.055..273.351 rows=60919 loops=1)
Filter: (active <> 0::numeric)
-> Hash (cost=1038.54..1038.54 rows=143
width=48) (actual time=124.746..124.746 rows=332 loops=1)
-> Nested Loop (cost=77.89..1038.54
rows=143 width=48) (actual time=46.253..123.164 rows=332 loops=1)
-> Hash Join (cost=77.89..563.45
rows=105 width=49) (actual time=42.417..109.635 rows=336 loops=1)
Hash Cond: (t3.id =
t11.slot_id)
-> Seq Scan on loop_slot t3
(cost=0.00..380.55 rows=9241 width=16) (actual time=0.049..42.454 rows=8950
loops=1)
Filter: (active <>
0::numeric)
-> Hash (cost=75.90..75.90
rows=159 width=33) (actual time=32.526..32.526 rows=376 loops=1)
-> Nested Loop
(cost=0.00..75.90 rows=159 width=33) (actual time=21.187..30.820 rows=376
loops=1)
-> Nested Loop
(cost=0.00..45.17 rows=4 width=24) (actual time=15.635..15.847 rows=4 loops=1)
-> Index
Scan using idx_day_part_du on day_part t10 (cost=0.00..7.42 rows=6 width=16)
(actual time=5.379..5.413 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=1.474..1.477 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.52 rows=93
width=25) (actual time=1.416..3.037 rows=94 loops=4)
Index
Cond: (t11.skin_id = t2.id)
Filter:
(t11.active <> 0::numeric)
-> Index Scan using
idx_schedule_owner_resource_id on schedule t7 (cost=0.00..4.51 rows=1
width=24) (actual time=0.022..0.026 rows=1 loops=336)
Index Cond:
(t7.owner_resource_id = t3.id)
Filter: (t7.active <>
0::numeric)
-> Index Scan using content_pkey on content t9
(cost=0.00..4.17 rows=1 width=16) (actual time=0.011..0.015 rows=1 loops=1104)
Index Cond: (t9.id = t8.content_id)
Total runtime: 1273.601 ms
(38 rows)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
