Query slow after analyse on postgresql 8.2 - Mailing list pgsql-performance

From Kaufhold, Christian (LFD)
Subject Query slow after analyse on postgresql 8.2
Date
Msg-id 0694BCCA1B37BE4B8FEB01807799BD540142B47F@BLFD_2.lfd.bayern.de
Whole thread Raw
Responses Re: Query slow after analyse on postgresql 8.2  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance

Hi,

I am new to this list so please forgive me if it not fits the standards.

I have the following query that I run agains postgresql 8.2:

select distinct
  m.koid,
  m.name,
  m.farbe,
  m.aktennummer,
  m.durchgefuehrt_von,
  m.durchgefuehrt_bis,
  rf.bezeichnung as rf_bezeichnung,
  mt.bezeichnung as mt_bezeichnung,
  wl_farben.wert,
  v_adr.text_lkr,
  v_adr.text_gemeinde
from
 (((((( boden.massnahmeobjekt m left join boden.massnahmengruppe mg on m.massnahmengruppe_koid=mg.koid)
 left join boden.th_referate rf on mg.angelegt_von_referat=rf.th_id)
 left join boden.th_massnahmentyp mt on m.massnahmentyp=mt.th_id)
 left join boden.wl_farben wl_farben on m.farbe=wl_farben.wl_id)
 left join boden_views.v_z_lc_flst v_flst on m.koid=v_flst.koid)
 left join boden_views.v_z_lc_adresse v_adr on m.koid=v_adr.koid)
where m.aktennummer ~* 'M\\-2009\\-1'   
order by koid asc limit 100

-----------------
It takes a around 10 secs to complete with the following plan:

----------------

Limit  (cost=128494.42..128494.69 rows=9 width=1212) (actual time=12463.236..12464.675 rows=100 loops=1)
  ->  Unique  (cost=128494.42..128494.69 rows=9 width=1212) (actual time=12463.206..12464.183 rows=100 loops=1)
        ->  Sort  (cost=128494.42..128494.44 rows=9 width=1212) (actual time=12463.178..12463.490 rows=123 loops=1)
              Sort Key: m.koid, m.name, m.farbe, m.aktennummer, m.durchgefuehrt_von, m.durchgefuehrt_bis, rf.bezeichnung, mt.bezeichnung, wl_farben.wert, t2.bezeichnung, t3.bezeichnung

              ->  Hash Left Join  (cost=119377.13..128494.28 rows=9 width=1212) (actual time=10475.870..12416.672 rows=3922 loops=1)

                    Hash Cond: (m.koid = lc.koid)
                    ->  Nested Loop Left Join  (cost=26.59..5848.52 rows=3 width=1148) (actual time=1.697..1711.535 rows=3813 loops=1)

                          ->  Nested Loop Left Join  (cost=26.59..5847.53 rows=3 width=1156) (actual time=1.664..1632.871 rows=3813 loops=1)

                                ->  Nested Loop Left Join  (cost=26.59..5846.68 rows=3 width=1152) (actual time=1.617..1538.819 rows=3813 loops=1)

                                      ->  Nested Loop Left Join  (cost=0.00..3283.05 rows=1 width=1148) (actual time=1.267..1352.254 rows=3694 loops=1)

                                            ->  Nested Loop Left Join  (cost=0.00..3282.77 rows=1 width=1120) (actual time=1.230..1232.264 rows=3694 loops=1)

                                                  ->  Nested Loop Left Join  (cost=0.00..3274.48 rows=1 width=1124) (actual time=1.089..1143.501 rows=3694 loops=1)

                                                        Join Filter: (m.massnahmentyp = mt.th_id)
                                                        ->  Nested Loop Left Join  (cost=0.00..3273.03 rows=1 width=1100) (actual time=0.999..671.405 rows=3694 loops=1)

                                                              Join Filter: (m.farbe = wl_farben.wl_id)
                                                              ->  Seq Scan on massnahmeobjekt m  (cost=0.00..3271.88 rows=1 width=1068) (actual time=0.909..425.324 rows=3694 loops=1)

                                                                    Filter: ((aktennummer)::text ~* 'M\\-2009\\-1'::text)

                                                              ->  Seq Scan on wl_farben  (cost=0.00..1.07 rows=7 width=36) (actual time=0.005..0.024 rows=7 loops=3694)

                                                        ->  Seq Scan on th_massnahmentyp mt  (cost=0.00..1.20 rows=20 width=40) (actual time=0.003..0.060 rows=20 loops=3694)

                                                  ->  Index Scan using idx_massnahmengruppe_koid on massnahmengruppe mg  (cost=0.00..8.28 rows=1 width=12) (actual time=0.009..0.012 rows=1 loops=3694)

--------------------------
But when I run analyse the same query runs for hours. (See eyplain output below)
--------------------

Limit  (cost=111795.21..111795.24 rows=1 width=149) (actual time=10954094.322..10954095.612 rows=100 loops=1)
  ->  Unique  (cost=111795.21..111795.24 rows=1 width=149) (actual time=10954094.316..10954095.165 rows=100 loops=1)
        ->  Sort  (cost=111795.21..111795.22 rows=1 width=149) (actual time=10954094.310..10954094.600 rows=123 loops=1)

              Sort Key: m.koid, m.name, m.farbe, m.aktennummer, m.durchgefuehrt_von, m.durchgefuehrt_bis, rf.bezeichnung, mt.bezeichnung, wl_farben.wert, t2.bezeichnung, t3.bezeichnung

              ->  Nested Loop Left Join  (cost=101312.40..111795.20 rows=1 width=149) (actual time=7983.197..10954019.963 rows=3922 loops=1)

                    Join Filter: (m.koid = lc.koid)
                    ->  Nested Loop Left Join  (cost=0.00..3291.97 rows=1 width=119) (actual time=1.083..2115.512 rows=3813 loops=1)

                          ->  Nested Loop Left Join  (cost=0.00..3291.69 rows=1 width=115) (actual time=0.980..2018.008 rows=3813 loops=1)

                                ->  Nested Loop Left Join  (cost=0.00..3283.41 rows=1 width=119) (actual time=0.868..1874.309 rows=3813 loops=1)

                                      Join Filter: (m.massnahmentyp = mt.th_id)
                                      ->  Nested Loop Left Join  (cost=0.00..3281.96 rows=1 width=105) (actual time=0.844..1394.628 rows=3813 loops=1)

                                            Join Filter: (m.farbe = wl_farben.wl_id)
                                            ->  Nested Loop Left Join  (cost=0.00..3280.80 rows=1 width=94) (actual time=0.825..1168.177 rows=3813 loops=1)

                                                  ->  Nested Loop Left Join  (cost=0.00..3280.47 rows=1 width=102) (actual time=0.808..1069.334 rows=3813 loops=1)

                                                        ->  Nested Loop Left Join  (cost=0.00..3280.18 rows=1 width=98) (actual time=0.694..918.863 rows=3813 loops=1)

                                                              ->  Seq Scan on massnahmeobjekt m  (cost=0.00..3271.88 rows=1 width=94) (actual time=0.387..577.771 rows=3694 loops=1)

                                                                    Filter: ((aktennummer)::text ~* 'M\\-2009\\-1'::text)

                                                              ->  Index Scan using idx_boden_lc_flst_koid on lc_flst lc  (cost=0.00..8.30 rows=1 width=12) (actual time=0.060..0.065 rows=1 loops=3694)

                                                                    Index Cond: (m.koid = lc.koid)
                                                        ->  Index Scan using th_meta_vagmk_pkey on th_meta_vagmk t1  (cost=0.00..0.27 rows=1 width=16) (actual time=0.022..0.025 rows=1 loops=3813)

----------------------
Thanks in advance for any help.
Christian Kaufhold

pgsql-performance by date:

Previous
From: "jgardner@jonathangardner.net"
Date:
Subject: Re: PostgreSQL as a local in-memory cache
Next
From: Dimitri Fontaine
Date:
Subject: Re: Parallel queries for a web-application |performance testing