[GENERAL] 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 ~~* 'j...@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 ~~* 'j...@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)
Re: [GENERAL] Strange Planner Issues
On Wed, Jul 29, 2009 at 8:01 PM, Jake Stridej...@omelett.es wrote: 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. - 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 ~~* 'j...@omelett.es'::text) AND (type = 'E'::bpchar)) - 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 ~~* 'j...@omelett.es'::text)) They don't look like the same data from here. One has no matching records in this table and the other has over 700. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
