Re: 8.4 optimization regression?
От | Mark Kirkwood |
---|---|
Тема | Re: 8.4 optimization regression? |
Дата | |
Msg-id | 4E548A7D.6090003@catalyst.net.nz обсуждение исходный текст |
Ответ на | Re: 8.4 optimization regression? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: 8.4 optimization regression?
(Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
|
Список | pgsql-performance |
On 24/08/11 15:15, Tom Lane wrote: > > Hmmm ... this is structurally a pretty simple query, so I'm surprised > that 8.3 and 8.4 see it very much differently. The relation-level > estimates and plan choices are very nearly the same; the only thing > that's changed much is the estimates of the join sizes, and there were > not that many changes in the join selectivity estimation for simple > inner joins. I wonder whether you are seeing a bad side-effect of this > patch: > > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7f3eba30 > Here is what the plan looks like with that patch reversed (it is back to 8.3 speed too). QUERY PLAN 8.4 - 7f3eba30 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=948567.18..1882454.51 rows=349702 width=0) (actual time=12320.037..38146.697 rows=217427 loops=1) Hash Cond: (cm.person_id = p.person_id) -> Hash Join (cost=791689.32..1702481.12 rows=581887 width=4) (actual time=7492.004..32727.783 rows=248441 loops=1) Hash Cond: (cm.corresp_master_id = c.corresp_master_id) -> Seq Scan on correspondence_master cm (cost=0.00..777460.25 rows=34003380 width=12) (actual time=0.016..8977.181 rows=33960209 loops=1) Filter: (person_id IS NOT NULL) -> Hash (cost=783297.43..783297.43 rows=671351 width=8) (actual time=7375.019..7375.019 rows=354456 loops=1) -> Hash Join (cost=231577.28..783297.43 rows=671351 width=8) (actual time=6374.538..7257.067 rows=354456 loops=1) Hash Cond: (c.generated_audit_id = gen_al.audit_id) -> Bitmap Heap Scan on correspondence c (cost=77121.49..532445.85 rows=4247118 width=16) (actual time=742.738..2790.225 rows=5293603 loops=1) Recheck Cond: (corresp_type_id = ANY ('{CL11,CL11A,CL12,CL15,CL15A,CL16,DM_1,DM_2}'::text[])) -> Bitmap Index Scan on corresp_type_fk (cost=0.00..76059.71 rows=4247118 width=0) (actual time=708.164..708.164 rows=5293603 loops=1) Index Cond: (corresp_type_id = ANY ('{CL11,CL11A,CL12,CL15,CL15A,CL16,DM_1,DM_2}'::text[])) -> Hash (cost=108073.47..108073.47 rows=2827066 width=8) (actual time=2759.145..2759.145 rows=2819891 loops=1) -> Index Scan using audit_log_audit_timestamp on audit_log gen_al (cost=0.00..108073.47 rows=2827066 width=8) (actual time=0.085..1800.175 rows=2819891 loops=1) Index Cond: (audit_timestamp > '2011-02-19 13:05:00'::timestamp without time zone) -> Hash (cost=115044.00..115044.00 rows=2549829 width=4) (actual time=4827.310..4827.310 rows=3101177 loops=1) -> Seq Scan on person p (cost=0.00..115044.00 rows=2549829 width=4) (actual time=0.061..3600.767 rows=3101177 loops=1) Filter: (active AND (NOT exclude_walklist_alt) AND (postal_address_id IS NULL) AND (NOT unpublished) AND (enrolment_status_id = ANY ('{E,T}'::text[])) AND (person_type = ANY ('{M,D,O}'::text[]))) Total runtime: 38171.865 ms Cheers Mark
В списке pgsql-performance по дате отправления:
Следующее
От: Venkat BalajiДата:
Сообщение: How to track number of connections and hosts to Postgres cluster