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 по дате отправления:

Предыдущее
От: Mark Kirkwood
Дата:
Сообщение: Re: 8.4 optimization regression?
Следующее
От: Venkat Balaji
Дата:
Сообщение: How to track number of connections and hosts to Postgres cluster