Strange Planner Issues

Hi,

I have 2 databases running on the same server. One is a dump of the
other, however the query plans for the same query on the same tables
in each database is wildly different and I cannot work out why.

The first result below is for the dump of the database and executes in
a reasonable time. The second is on the same server, just different
DB.

Does anybody have any ideas/pointers. Both have been vacuum analyzed.

      QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3299.79..3299.80 rows=1 width=8) (actual
time=114.311..114.312 rows=1 loops=1)
  ->  Nested Loop Left Join  (cost=0.00..3299.79 rows=1 width=8)
(actual time=114.196..114.196 rows=0 loops=1)
        ->  Nested Loop Left Join  (cost=0.00..3296.49 rows=1
width=16) (actual time=114.193..114.193 rows=0 loops=1)
              Filter: ((u.username IS NULL) OR u.enabled)
              ->  Nested Loop Left Join  (cost=0.00..3295.99 rows=1
width=16) (actual time=114.190..114.190 rows=0 loops=1)
                    ->  Nested Loop Left Join  (cost=0.00..3293.09
rows=1 width=16) (actual time=114.188..114.188 rows=0 loops=1)
                          ->  Nested Loop  (cost=0.00..3290.19 rows=1
width=16) (actual time=114.184..114.184 rows=0 loops=1)
                                ->  Seq Scan on
person_contact_methods e  (cost=0.00..3281.89 rows=1 width=8) (actual
time=114.181..114.181 rows=0 loops=1)
                                      Filter: (main AND
((contact)::text ~~* 'jake@omelett.es'::text) AND (type =
'E'::bpchar))
                                ->  Index Scan using person_pkey on
people p  (cost=0.00..8.28 rows=1 width=16) (never executed)
                                      Index Cond: (p.id = e.person_id)
                                      Filter: (p.usercompanyid = 74607::bigint)
                          ->  Index Scan using
person_contact_methods_person_id_index on person_contact_methods m
(cost=0.00..2.89 rows=1 width=8) (never executed)
                                Index Cond: (p.id = m.person_id)
                                Filter: (m.main AND (m.type = 'M'::bpchar))
                    ->  Index Scan using
person_contact_methods_person_id_index on person_contact_methods ph
(cost=0.00..2.89 rows=1 width=8) (never executed)
                          Index Cond: (p.id = ph.person_id)
                          Filter: (ph.main AND (ph.type = 'T'::bpchar))
              ->  Index Scan using users_person_id_key on users u
(cost=0.00..0.49 rows=1 width=28) (never executed)
                    Index Cond: (u.person_id = p.id)
        ->  Index Scan using company_id_key on organisations org
(cost=0.00..3.28 rows=1 width=8) (never executed)
              Index Cond: (org.id = p.organisation_id)
 Total runtime: 115.119 ms

           QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27525.76..27525.77 rows=1 width=8) (actual
time=60573.233..60573.234 rows=1 loops=1)
  ->  Nested Loop  (cost=24244.80..27525.75 rows=1 width=8) (actual
time=60497.421..60573.169 rows=2 loops=1)
        Join Filter: ("inner".id = "outer".person_id)
        ->  Seq Scan on person_contact_methods e  (cost=0.00..2942.06
rows=1 width=8) (actual time=3.718..184.602 rows=772 loops=1)
              Filter: (("type" = 'E'::bpchar) AND main AND
((contact)::text ~~* 'jake@omelett.es'::text))
        ->  Merge Left Join  (cost=24244.80..24537.57 rows=3690
width=8) (actual time=17.930..76.188 rows=3495 loops=772)
              Merge Cond: ("outer".id = "inner".person_id)
              ->  Merge Left Join  (cost=22122.13..22354.98 rows=3690
width=8) (actual time=14.359..59.647 rows=3495 loops=772)
                    Merge Cond: ("outer".id = "inner".person_id)
                    ->  Merge Left Join  (cost=17317.28..17366.04
rows=3690 width=8) (actual time=2.179..12.455 rows=3495 loops=772)
                          Merge Cond: ("outer".id = "inner".person_id)
                          Filter: (("inner".username IS NULL) OR
"inner".enabled)
                          ->  Sort  (cost=16771.51..16780.74
rows=3690 width=8) (actual time=0.130..2.082 rows=3499 loops=772)
                                Sort Key: p.id
                                ->  Nested Loop Left Join
(cost=29.91..16552.89 rows=3690 width=8) (actual time=2.979..91.991
rows=3499 loops=1)
                                      ->  Bitmap Heap Scan on people
p  (cost=29.91..3186.38 rows=3690 width=16) (actual time=2.867..30.251
rows=3499 loops=1)
                                            Recheck Cond:
(usercompanyid = 74607::bigint)
                                            ->  Bitmap Index Scan on
person_usercompanyid  (cost=0.00..29.91 rows=3690 width=0) (actual
time=2.717..2.717 rows=10241 loops=1)
                                                  Index Cond:
(usercompanyid = 74607::bigint)
                                      ->  Index Scan using
company_id_key on organisations org  (cost=0.00..3.61 rows=1 width=8)
(actual time=0.013..0.014 rows=1 loops=3499)
                                            Index Cond: (org.id =
"outer".organisation_id)
                          ->  Sort  (cost=545.77..560.12 rows=5740
width=31) (actual time=0.115..2.333 rows=4416 loops=772)
                                Sort Key: u.person_id
                                ->  Seq Scan on users u
(cost=0.00..187.40 rows=5740 width=31) (actual time=0.009..74.287
rows=4416 loops=1)
                    ->  Sort  (cost=4804.84..4887.64 rows=33119
width=8) (actual time=0.303..25.672 rows=29729 loops=772)
                          Sort Key: ph.person_id
                          ->  Bitmap Heap Scan on
person_contact_methods ph  (cost=339.72..2021.70 rows=33119 width=8)
(actual time=19.735..96.793 rows=29729 loops=1)
                                Recheck Cond: ("type" = 'T'::bpchar)
                                Filter: main
                                ->  Bitmap Index Scan on
person_contact_methods_type_main  (cost=0.00..339.72 rows=33119
width=0) (actual time=19.130..19.130 rows=30192 loops=1)
                                      Index Cond: (("type" =
'T'::bpchar) AND (main = true))
              ->  Sort  (cost=2122.67..2146.68 rows=9601 width=8)
(actual time=0.061..5.231 rows=10225 loops=772)
                    Sort Key: m.person_id
                    ->  Bitmap Heap Scan on person_contact_methods m
(cost=99.60..1487.62 rows=9601 width=8) (actual time=5.053..26.357
rows=10225 loops=1)
                          Recheck Cond: ("type" = 'M'::bpchar)
                          Filter: main
                          ->  Bitmap Index Scan on
person_contact_methods_type_main  (cost=0.00..99.60 rows=9601 width=0)
(actual time=4.759..4.759 rows=10445 loops=1)
                                Index Cond: (("type" = 'M'::bpchar)
AND (main = true))
 Total runtime: 60577.031 ms