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@xxxxxxxxxx'::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@xxxxxxxxxx'::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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
