X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BCCE7475F1B for ; Tue, 29 Apr 2003 12:04:34 -0400 (EDT) Received: from mail.deltav.hu (mail.deltav.hu [213.163.0.192]) by postgresql.org (Postfix) with ESMTP id 73DD4475F34 for ; Tue, 29 Apr 2003 12:03:58 -0400 (EDT) Received: from fejleszt2 ([213.163.10.103]) by mail.deltav.hu (Netscape Messaging Server 3.6) with SMTP id AAA2255 for ; Tue, 29 Apr 2003 18:03:55 +0200 Message-ID: <011101c30e68$f056bdb0$0a03a8c0@fejleszt2> From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" To: References: <00db01c30e54$1ab272d0$0a03a8c0@fejleszt2> <26550.1051627993@sss.pgh.pa.us> Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1 Date: Tue, 29 Apr 2003 18:03:48 +0200 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_010E_01C30E79.AEFC2E20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Spam-Status: No, hits=-11.5 required=5.0 tests=BAYES_20,NO_DNS_FOR_FROM,QUOTED_EMAIL_TEXT,REFERENCES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/252 X-Sequence-Number: 1758 This is a multi-part message in MIME format. ------=_NextPart_000_010E_01C30E79.AEFC2E20 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit Sure, thanks for your interest :) hope these help. G. -- while (!asleep()) sheep++; ---------------------------- cut here ------------------------------ ----- Original Message ----- From: "Tom Lane" Sent: Tuesday, April 29, 2003 4:53 PM > "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" writes: > > A nasty query and its EXPLAINs are here. Read on at your own risk :) > > It's pretty much unreadable because of the way your mailer folded, > spindled, and mutilated the EXPLAIN output :-( > > Could you resend in a more legible format? Maybe append the explain > output as an attachment, if you can't get the mailer to leave its > formatting alone otherwise. ------=_NextPart_000_010E_01C30E79.AEFC2E20 Content-Type: application/octet-stream; name="szkb" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="szkb" SELECT *, kerekit_penznem (netto_ertek*(afa_szazalek/100), penznem)=20 AS afa_ertek, kerekit_penznem (netto_ertek*(1+afa_szazalek/100), penznem)=20 AS brutto_ertek FROM ( SELECT szamla,=20 kerekit_penznem( elsodl_netto_ertek*COALESCE(deka,1)/COALESCE(dekb,1), penznem)=20 AS netto_ertek, konyvelesi_tetelcsoport, afa, afa_szazalek, penznem FROM=20 (SELECT szt.szamla,=20 kerekit_penznem(sum(szt.netto_egysegar * szt.mennyiseg), penznem)=20 AS elsodl_netto_ertek, konyvelesi_tetelcsoport(szt.szamla, szt.tetelszam)=20 AS konyvelesi_tetelcsoport, szt.afa, afa.ertek AS afa_szazalek, szamla.penznem AS sz_penznem, szamla.teljesites AS sz_teljesites, arf_a.deviza_kozeparfolyam as deka, arf_b.deviza_kozeparfolyam as dekb, foo_valuta AS penznem FROM szamla_tetele szt=20 LEFT JOIN szamla ON (szamla =3D szamla.az)=20 LEFT JOIN afa ON (afa.az =3D szt.afa) LEFT JOIN arfolyam arf_a=20 ON (arf_a.ervenyes =3D=20 (SELECT ervenyes FROM arfolyam WHERE ervenyes<=3Dszamla.teljesites AND valuta =3D szamla.penznem ORDER BY 1 DESC LIMIT 1) AND szamla.penznem=3Darf_a.valuta) JOIN=09 (SELECT az AS foo_valuta FROM valuta) AS valuta ON (true) LEFT JOIN arfolyam arf_b=20 ON (arf_b.valuta=3Dfoo_valuta AND arf_b.ervenyes =3D=20 (SELECT ervenyes FROM arfolyam=20 WHERE ervenyes<=3Dszamla.teljesites AND valuta =3D foo_valuta ORDER BY 1 DESC LIMIT 1) ) WHERE (NOT szt.archiv) AND (foo_valuta =3D 4 or arf_b.valuta notnull ) GROUP BY szt.szamla, konyvelesi_tetelcsoport, szt.afa,=20 sz_penznem, sz_teljesites, afa.ertek,=20 arf_a.deviza_kozeparfolyam, arf_b.deviza_kozeparfolyam,=20 foo_valuta, penznem ) foo=20 ) bar WHERE szamla =3D 2380; ------------------------------------------------------------------------- ---------------------------- 7.2.1 PLAN --------------------------------- ------------------------------------------------------------------------- NOTICE: QUERY PLAN: Subquery Scan foo (cost=3D488.97..490.94 rows=3D8 width=3D104) (actual tim= e=3D94.77..109.10 rows=3D12 loops=3D1) -> Aggregate (cost=3D488.97..490.94 rows=3D8 width=3D104) (actual time=3D= 89.29..92.05 rows=3D12 loops=3D1) -> Group (cost=3D488.97..490.74 rows=3D79 width=3D104) (actual time= =3D88.13..88.59 rows=3D12 loops=3D1) -> Sort (cost=3D488.97..488.97 rows=3D79 width=3D104) (actual time=3D88.= 09..88.13 rows=3D12 loops=3D1) -> Nested Loop (cost=3D1.05..486.50 rows=3D79 width=3D104) (actual t= ime=3D28.23..86.20 rows=3D12 loops=3D1) -> Nested Loop (cost=3D1.05..150.19 rows=3D79 width=3D84) (actual time= =3D12.68..25.41 rows=3D12 loops=3D1) -> Nested Loop (cost=3D1.05..135.52 rows=3D13 width=3D80) (actual t= ime=3D12.60..24.80 rows=3D2 loops=3D1) -> Hash Join (cost=3D1.05..79.59 rows=3D13 width=3D60) (actual time=3D= 0.55..0.80 rows=3D2 loops=3D1) -> Nested Loop (cost=3D0.00..78.31 rows=3D13 width=3D46) (actual t= ime=3D0.23..0.42 rows=3D2 loops=3D1) -> Index Scan using szml_ttl_szml on szamla_tetele szt (cost=3D0.00..= 3.51 rows=3D13 width=3D34) (actual time=3D0.11..0.16 rows=3D2 loops=3D1) -> Index Scan using szamla_az_key on szamla (cost=3D0.00..5.70 rows= =3D1 width=3D12) (actual time=3D0.07..0.09 rows=3D1 loops=3D2) -> Hash (cost=3D1.04..1.04 rows=3D4 width=3D14) (actual time=3D0.1= 4..0.14 rows=3D0 loops=3D1) -> Seq Scan on afa (cost=3D0.00..1.04 rows=3D4 width=3D14) (actual ti= me=3D0.08..0.11 rows=3D4 loops=3D1) -> Index Scan using arfolyam_ervenyes on arfolyam arf_a (cost=3D0.00..= 3.63 rows=3D3 width=3D20) (actual time=3D0.01..0.01 rows=3D0 loops=3D2) SubPlan -> Limit (cost=3D0.00..0.17 rows=3D1 width=3D4) (actual time=3D11.= 92..11.92 rows=3D0 loops=3D2) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=3D0.= 00..13.30 rows=3D79 width=3D4) (actual time=3D11.90..11.90 rows=3D0 loops= =3D2) -> Limit (cost=3D0.00..0.17 rows=3D1 width=3D4) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=3D0.= 00..13.30 rows=3D79 width=3D4) -> Seq Scan on valuta (cost=3D0.00..1.06 rows=3D6 width=3D4) (actua= l time=3D0.02..0.11 rows=3D6 loops=3D2) -> Index Scan using arfolyam_ervenyes on arfolyam arf_b (cost=3D0.00..3= .63 rows=3D3 width=3D20) (actual time=3D0.04..0.10 rows=3D3 loops=3D12) SubPlan -> Limit (cost=3D0.00..0.17 rows=3D1 width=3D4) (actual time=3D4.35= ..4.39 rows=3D1 loops=3D12) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=3D0.0= 0..13.30 rows=3D79 width=3D4) (actual time=3D4.33..4.37 rows=3D2 loops=3D12) -> Limit (cost=3D0.00..0.17 rows=3D1 width=3D4) -> Index Scan Backward using arfolyam_ervenyes on arfolyam (cost=3D0.0= 0..13.30 rows=3D79 width=3D4) Total runtime: 111.48 msec ------------------------------------------------------------------------- ---------------------------- 7.3.2 PLAN --------------------------------- ------------------------------------------------------------------------- = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------- Subquery Scan foo (cost=3D14542.01..15448.46 rows=3D3022 width=3D123) (ac= tual time=3D2264.36..2282.17 rows=3D12 loops=3D1) -> Aggregate (cost=3D14542.01..15448.46 rows=3D3022 width=3D123) (actu= al time=3D2257.70..2261.08 rows=3D12 loops=3D1) -> Group (cost=3D14542.01..15372.92 rows=3D30215 width=3D123) (a= ctual time=3D2256.31..2256.84 rows=3D12 loops=3D1) -> Sort (cost=3D14542.01..14617.55 rows=3D30215 width=3D12= 3) (actual time=3D2256.27..2256.31 rows=3D12 loops=3D1) Sort Key: szt.szamla, konyvelesi_tetelcsoport(szt.szam= la, szt.tetelszam), szt.afa, szamla.penznem, szamla.teljesites, afa.ertek, = arf_a.deviza_kozeparfolyam, arf_b.deviza_kozeparfolyam, public.valuta.az -> Merge Join (cost=3D4755.50..11038.79 rows=3D30215= width=3D123) (actual time=3D80.88..2254.96 rows=3D12 loops=3D1) Merge Cond: ("outer".az =3D "inner".valuta) Join Filter: ("inner".ervenyes =3D (subplan)) Filter: (("outer".az =3D 4) OR ("inner".valuta I= S NOT NULL)) -> Sort (cost=3D4676.19..4751.73 rows=3D30215 = width=3D103) (actual time=3D56.41..56.44 rows=3D12 loops=3D1) Sort Key: public.valuta.az -> Nested Loop (cost=3D433.35..1375.92 r= ows=3D30215 width=3D103) (actual time=3D55.78..56.18 rows=3D12 loops=3D1) -> Merge Join (cost=3D433.35..469.= 47 rows=3D30 width=3D99) (actual time=3D55.68..55.72 rows=3D2 loops=3D1) Merge Cond: ("outer".penznem = =3D "inner".valuta) Join Filter: ("inner".ervenyes= =3D (subplan)) -> Sort (cost=3D354.05..354.= 12 rows=3D30 width=3D79) (actual time=3D32.63..32.64 rows=3D2 loops=3D1) Sort Key: szamla.penznem -> Hash Join (cost=3D1= 21.67..353.30 rows=3D30 width=3D79) (actual time=3D32.37..32.49 rows=3D2 lo= ops=3D1) Hash Cond: ("outer= ".afa =3D "inner".az) -> Merge Join (c= ost=3D120.62..352.09 rows=3D30 width=3D58) (actual time=3D32.07..32.16 rows= =3D2 loops=3D1) Merge Cond: = ("outer".az =3D "inner".szamla) -> Sort (c= ost=3D120.62..123.85 rows=3D1291 width=3D12) (actual time=3D25.62..27.62 ro= ws=3D1285 loops=3D1) Sort K= ey: szamla.az -> Se= q Scan on szamla (cost=3D0.00..53.91 rows=3D1291 width=3D12) (actual time= =3D0.04..16.46 rows=3D1314 loops=3D1) -> Index Sc= an using szamla_tetele_pkey on szamla_tetele szt (cost=3D0.00..218.88 rows= =3D30 width=3D46) (actual time=3D0.13..0.18 rows=3D2 loops=3D1) Index = Cond: (szamla =3D 2380) Filter= : (NOT archiv) -> Hash (cost=3D= 1.04..1.04 rows=3D4 width=3D21) (actual time=3D0.10..0.10 rows=3D0 loops=3D= 1) -> Seq Scan= on afa (cost=3D0.00..1.04 rows=3D4 width=3D21) (actual time=3D0.04..0.07 = rows=3D4 loops=3D1) -> Sort (cost=3D79.30..82.19= rows=3D1155 width=3D20) (actual time=3D22.93..22.93 rows=3D1 loops=3D1) Sort Key: arf_a.valuta -> Seq Scan on arfolyam= arf_a (cost=3D0.00..20.55 rows=3D1155 width=3D20) (actual time=3D0.03..9.= 66 rows=3D1155 loops=3D1) SubPlan -> Limit (cost=3D0.00..0.1= 6 rows=3D1 width=3D4) (never executed) -> Index Scan Backwar= d using arfolyam_ervenyes on arfolyam (cost=3D0.00..12.17 rows=3D77 width= =3D4) (never executed) Index Cond: (erv= enyes <=3D $0) Filter: (valuta = =3D $1) -> Seq Scan on valuta (cost=3D0.00= ..20.00 rows=3D1000 width=3D4) (actual time=3D0.02..0.06 rows=3D6 loops=3D2) -> Sort (cost=3D79.30..82.19 rows=3D1155 width= =3D20) (actual time=3D21.98..27.54 rows=3D2309 loops=3D1) Sort Key: arf_b.valuta -> Seq Scan on arfolyam arf_b (cost=3D0.= 00..20.55 rows=3D1155 width=3D20) (actual time=3D0.03..9.89 rows=3D1155 loo= ps=3D1) SubPlan -> Limit (cost=3D0.00..0.16 rows=3D1 width= =3D4) (actual time=3D0.88..0.91 rows=3D1 loops=3D2310) -> Index Scan Backward using arfolyam_e= rvenyes on arfolyam (cost=3D0.00..12.17 rows=3D77 width=3D4) (actual time= =3D0.87..0.90 rows=3D2 loops=3D2310) Index Cond: (ervenyes <=3D $0) Filter: (valuta =3D $2) Total runtime: 2287.30 msec (47 rows) ------=_NextPart_000_010E_01C30E79.AEFC2E20--