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
