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