X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 187CF329D14 for ; Wed, 29 Sep 2004 18:44:36 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34983-06 for ; Wed, 29 Sep 2004 17:44:15 +0000 (GMT) Received: from fe08.axelero.hu (fe08.axelero.hu [195.228.240.96]) by svr1.postgresql.org (Postfix) with ESMTP id 735E432A014 for ; Wed, 29 Sep 2004 18:43:56 +0100 (BST) Received: from fe08 (localhost-02 [127.0.2.1]) by fe08.axelero.hu (8.12.11/8.12.11) with SMTP id i8THgkiv045880; Wed, 29 Sep 2004 19:42:46 +0200 (CEST) Received: from fe08.axelero.hu [127.0.2.1] via SMTP gateway by fe08 [195.228.240.96]; id A0B33542AB7 at Wed, 29 Sep 2004 19:42:46 +0200 Received: from fejleszt4 (121-248-182-81.adsl-fixip.axelero.hu [81.182.248.121]) by fe08.axelero.hu (8.12.11/8.12.11) with SMTP id i8THgi4j045846; Wed, 29 Sep 2004 19:42:44 +0200 (CEST) Message-ID: <030201c4a64b$ef96ec20$0403a8c0@fejleszt4> From: =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= To: Cc: Subject: stubborn query confuses two different servers Date: Wed, 29 Sep 2004 19:44:13 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-RAVMilter-Version: 8.4.3(snapshot 20030217) (fe08.axelero.hu) Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by fe08.axelero.hu id i8THgi4j045846 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/329 X-Sequence-Number: 8457 Dear Gurus, Here is this strange query that can't find the optimum plan unless I disable some scan modes or change the costs. (A) is a 2x2.4GHz server with hw raid5 and v7.3.4 database. It chooses hashjoin. (B) is a 300MHz server with 7200rpm ide and v7.4.2 database. It chooses seqscan. If I disable hashjoin/seqscan+hashjoin+mergejoin, both choose index scan. (A) goes from 1000ms to 55ms (B) goes from 5000+ms to 300ms If your expert eyes could catch something missing (an index, analyze or something), I'd be greatly honoured :) Also, tips about which optimizer costs may be too high or too low are highly appreciated. As far as I fumbled with (B), disabling plans step by step got worse until after disabled all tree. Reducing random_page_cost from 2 to 1.27 or lower instantly activated the index scan, but I fear that it hurt most of our other queries. The faster server did not respond to any changes, even with rpc=3D1 and cpu_index_tuple_cost=3D0.0001, it chose hash join. All that I discovered is that both servers fail to find the right index (szlltlvl_ttl_szlltlvl) unless forced to. In hope of an enlightening answer, Yours, G. %----------------------- cut here -----------------------% -- QUERY: explain analyze -- 5000msec. rpc1.27-: 300 SELECT coalesce(szallitolevel,0) AS scope_kov_szallitolevel, CASE 'rakt=E1ros' WHEN 'rakt=E1ros' THEN szallitolevel_bejovo_e(szallitolev= el) WHEN 'sof=F5r' THEN 1027=3D(SELECT coalesce(sofor,0) FROM szallitolevel WHE= RE az=3Dszallitolevel) ELSE true END FROM (SELECT l.az AS szallitolevel FROM szallitolevel l, szallitolevel_tetele t WHERE szallitas=3D1504 AND allapot NOT IN (6,7,8) -- pakoland=F3 t=E9telekkel AND t.szallitolevel =3D l.az AND NOT t.archiv -- ha arch=EDv van, de most nincs, legf=F6ljebb k=F6v k=F6rben kibukik AND t.fajta IN (4,90,100) GROUP BY t.szallitolevel, l.az HAVING count(t.*)>0) t1 NATURAL FULL OUTER JOIN (SELECT szallitolevel, az AS pakolas FROM pakolas WHERE szallitasba=3D1504 = AND sztornozott_pakolas IS NULL) t2 WHERE pakolas IS NULL ORDER BY 2 DESC LIMIT 1; %----------------------- cut here -----------------------% -- plan of (A), hashjoin -- QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------------------------------- Limit (cost=3D2795.58..2795.58 rows=3D1 width=3D12) (actual time=3D1089.72..1089.72 rows=3D1 loops=3D1) -> Sort (cost=3D2795.58..2804.26 rows=3D3472 width=3D12) (actual time=3D1089.72..1089.72 rows=3D2 loops=3D1) Sort Key: szallitolevel_bejovo_e(szallitolevel) -> Merge Join (cost=3D2569.48..2591.39 rows=3D3472 width=3D12) (= actual time=3D1086.72..1089.67 rows=3D2 loops=3D1) Merge Cond: ("outer".szallitolevel =3D "inner".szallitolevel) Filter: ("inner".az IS NULL) -> Sort (cost=3D1613.43..1614.15 rows=3D288 width=3D12) (a= ctual time=3D1054.21..1054.26 rows=3D80 loops=3D1) Sort Key: t1.szallitolevel -> Subquery Scan t1 (cost=3D1572.82..1601.65 rows=3D= 288 width=3D12) (actual time=3D1050.72..1054.09 rows=3D80 loops=3D1) -> Aggregate (cost=3D1572.82..1601.65 rows=3D2= 88 width=3D12) (actual time=3D1050.70..1053.93 rows=3D80 loops=3D1) Filter: (count("*") > 0) -> Group (cost=3D1572.82..1594.44 rows= =3D2883 width=3D12) (actual time=3D1050.64..1052.98 rows=3D824 loops=3D1) -> Sort (cost=3D1572.82..1580.03 rows=3D2883 width=3D12) (actual time=3D1050.63..1051.24 rows=3D824 loops=3D= 1) Sort Key: t.szallitolevel, l.az -> Hash Join (cost=3D531.09..1407.13 rows=3D2883 width=3D12) (actual time=3D8.13..1048.89 rows=3D824 loops=3D1) Hash Cond: ("outer".szallitolevel =3D "inner".az) -> Index Scan using szallitolevel_tetele_me on szallitolevel_tetele t (cost=3D0.00..2.25 rows=3D167550 width=3D8) (actual time=3D0.18..871.77 rows=3D167888 loops=3D= 1) Filter: ((NOT archiv) AND ((fajta =3D 4) OR (fajta =3D 90) OR (fajta =3D 100))) -> Hash (cost=3D530.06..530.06 rows=3D411 width=3D4) (actual time=3D7.92..7.92 rows= =3D0 loops=3D1) -> Index Scan using szlltlvl_szllts on szallitolevel l (cost=3D0.00..530.06 rows=3D411 width=3D4) (actual time=3D0.04..7.81 rows=3D92 loops=3D1) Index Cond: (szallitas =3D 1504) Filter: ((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8)) -> Sort (cost=3D956.05..964.73 rows=3D3472 width=3D8) (act= ual time=3D27.80..30.24 rows=3D3456 loops=3D1) Sort Key: pakolas.szallitolevel -> Index Scan using pakolas_szallitasba on pakolas (cost=3D0.00..751.87 rows=3D3472 width=3D8) (actual time=3D0.10..21.68 rows= =3D3456 loops=3D1) Index Cond: (szallitasba =3D 1504) Filter: (sztornozott_pakolas IS NULL) Total runtime: 1090.30 msec (28 rows) -- plan of (A), disabled hashjoin -- QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------------------------------------------- Limit (cost=3D10616.86..10616.86 rows=3D1 width=3D12) (actual time=3D45.0= 4..45.04 rows=3D1 loops=3D1) -> Sort (cost=3D10616.86..10625.54 rows=3D3472 width=3D12) (actual time=3D45.04..45.04 rows=3D2 loops=3D1) Sort Key: szallitolevel_bejovo_e(szallitolevel) -> Merge Join (cost=3D10390.77..10412.67 rows=3D3472 width=3D12) (actual time=3D42.06..44.99 rows=3D2 loops=3D1) Merge Cond: ("outer".szallitolevel =3D "inner".szallitolevel) Filter: ("inner".az IS NULL) -> Sort (cost=3D9434.71..9435.43 rows=3D288 width=3D12) (a= ctual time=3D19.83..19.89 rows=3D80 loops=3D1) Sort Key: t1.szallitolevel -> Subquery Scan t1 (cost=3D9394.10..9422.93 rows=3D= 288 width=3D12) (actual time=3D16.39..19.71 rows=3D80 loops=3D1) -> Aggregate (cost=3D9394.10..9422.93 rows=3D2= 88 width=3D12) (actual time=3D16.39..19.56 rows=3D80 loops=3D1) Filter: (count("*") > 0) -> Group (cost=3D9394.10..9415.72 rows= =3D2883 width=3D12) (actual time=3D16.33..18.62 rows=3D824 loops=3D1) -> Sort (cost=3D9394.10..9401.31 rows=3D2883 width=3D12) (actual time=3D16.33..16.93 rows=3D824 loops=3D1) Sort Key: t.szallitolevel, l.az -> Nested Loop (cost=3D0.00..9228.41 rows=3D2883 width=3D12) (actual time=3D0.07..14.79 ro= ws=3D824 loops=3D1) -> Index Scan using szlltlvl_szllts on szallitolevel l (cost=3D0.00..530.06 rows=3D411 width= =3D4) (actual time=3D0.04..7.97 rows=3D92 loops=3D1) Index Cond: (szallitas =3D 1504) Filter: ((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8)) -> Index Scan using szlltlvl_ttl_szlltlvl on szallitolevel_tetele t (cost=3D0.00..20.99 rows= =3D12 width=3D8) (actual time=3D0.01..0.05 rows=3D9 loops=3D92) Index Cond: (t.szallitolevel =3D "outer".az) Filter: ((NOT archiv) AND ((fajta =3D 4) OR (fajta =3D 90) OR (fajta =3D 100))) -> Sort (cost=3D956.05..964.73 rows=3D3472 width=3D8) (act= ual tim e=3D17.76..20.18 rows=3D3456 loops=3D1) Sort Key: pakolas.szallitolevel -> Index Scan using pakolas_szallitasba on pakolas (cost=3D0.00..751.87 rows=3D3472 width=3D8) (actual time=3D0.02..11.56 rows= =3D3456 loops=3D1) Index Cond: (szallitasba =3D 1504) Filter: (sztornozott_pakolas IS NULL) Total runtime: 45.62 msec (27 rows) %----------------------- cut here -----------------------% -- plan of (B), seqscan -- QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------- Limit (cost=3D9132.84..9132.84 rows=3D1 width=3D8) (actual time=3D3958.144..3958.144 rows=3D0 loops=3D1) -> Sort (cost=3D9132.84..9142.66 rows=3D3928 width=3D8) (actual time=3D3958.132..3958.132 rows=3D0 loops=3D1) Sort Key: szallitolevel_bejovo_e(COALESCE(t1.szallitolevel, pakolas.szallitolevel)) -> Merge Full Join (cost=3D8834.40..8898.34 rows=3D3928 width=3D= 8) (actual time=3D3958.099..3958.099 rows=3D0 loops=3D1) Merge Cond: ("outer".szallitolevel =3D "inner".szallitolevel) Filter: ("inner".az IS NULL) -> Sort (cost=3D7737.61..7743.49 rows=3D2352 width=3D4) (a= ctual time=3D3798.439..3798.594 rows=3D85 loops=3D1) Sort Key: t1.szallitolevel -> Subquery Scan t1 (cost=3D7570.63..7605.91 rows=3D= 2352 width=3D4) (actual time=3D3796.553..3797.981 rows=3D85 loops=3D1) -> HashAggregate (cost=3D7570.63..7582.39 rows=3D2352 width=3D12) (actual time=3D3796.535..3797.493 rows=3D85 loops= =3D1) Filter: (count("*") > 0) -> Hash Join (cost=3D628.69..7552.99 rows=3D2352 width=3D12) (actual time=3D62.874..3785.188 rows=3D899 loops=3D= 1) Hash Cond: ("outer".szallitolevel = =3D "inner".az) -> Seq Scan on szallitolevel_tetele t (cost=3D0.00..6062.06 rows=3D167743 width=3D8) (actual time=3D0.236..307= 2.882 rows=3D167637 loops=3D1) Filter: ((NOT archiv) AND ((fajta =3D 4) OR (fajta =3D 90) OR (fajta =3D 100))) -> Hash (cost=3D627.86..627.86 rows=3D335 width=3D4) (actual time=3D54.973..54.973 rows=3D0 loops=3D1) -> Index Scan using szlltlvl_szllts on szallitolevel l (cost=3D0.00..627.86 rows=3D335 width= =3D4) (actual time=3D0.592..54.298 rows=3D91 loops=3D1) Index Cond: (szallitas = =3D 1504) Filter: ((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8)) -> Sort (cost=3D1096.79..1106.61 rows=3D3928 width=3D8) (a= ctual time=3D137.216..143.009 rows=3D3458 loops=3D1) Sort Key: pakolas.szallitolevel -> Index Scan using pakolas_szallitasba on pakolas (cost=3D0.00..862.30 rows=3D3928 width=3D8) (actual time=3D0.581..107.542 r= ows=3D3458 loops=3D1) Index Cond: (szallitasba =3D 1504) Filter: (sztornozott_pakolas IS NULL) Total runtime: 3971.008 ms (25 rows) -- plan of (B), disabled seqscan, hashjoin, mergejoin -- QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------ Limit (cost=3D100012622.51..100012622.51 rows=3D1 width=3D8) (actual time=3D295.102..295.102 rows=3D0 loops=3D1) -> Sort (cost=3D100012622.51..100012632.33 rows=3D3928 width=3D8) (act= ual time=3D295.091..295.091 rows=3D0 loops=3D1) Sort Key: szallitolevel_bejovo_e(COALESCE(t1.szallitolevel, pakolas.szallitolevel)) -> Merge Full Join (cost=3D100012324.08..100012388.02 rows=3D3928 width=3D8) (actual time=3D295.057..295.057 rows=3D0 loops=3D1) Merge Cond: ("outer".szallitolevel =3D "inner".szallitolevel) Filter: ("inner".az IS NULL) -> Sort (cost=3D11227.29..11233.17 rows=3D2352 width=3D4) = (actual time=3D139.055..139.200 rows=3D85 loops=3D1) Sort Key: t1.szallitolevel -> Subquery Scan t1 (cost=3D11060.30..11095.58 rows=3D2352 width=3D4) (actual time=3D137.166..138.588 rows=3D85 loops=3D1) -> HashAggregate (cost=3D11060.30..11072.06 rows=3D2352 width=3D12) (actual time=3D137.150..138.106 rows=3D85 loops=3D1) Filter: (count("*") > 0) -> Nested Loop (cost=3D0.00..11042.66 rows=3D2352 width=3D12) (actual time=3D14.451..127.809 rows=3D899 loops=3D1) -> Index Scan using szlltlvl_szllts on szallitolevel l (cost=3D0.00..627.86 rows=3D335 width=3D4) (actual time=3D0.579..53.503 rows=3D91 loops=3D1) Index Cond: (szallitas =3D 150= 4) Filter: ((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8)) -> Index Scan using szlltlvl_ttl_szlltlvl on szallitolevel_tetele t (cost=3D0.00..30.91 rows= =3D14 width=3D8) (actual time=3D0.232..0.714 rows=3D10 loops=3D91) Index Cond: (t.szallitolevel = =3D "outer".az) Filter: ((NOT archiv) AND ((fajta =3D 4) OR (fajta =3D 90) OR (fajta =3D 100))) -> Sort (cost=3D1096.79..1106.61 rows=3D3928 width=3D8) (a= ctual time=3D133.517..139.338 rows=3D3458 loops=3D1) Sort Key: pakolas.szallitolevel -> Index Scan using pakolas_szallitasba on pakolas (cost=3D0.00..862.30 rows=3D3928 width=3D8) (actual time=3D0.526..103.313 r= ows=3D3458 loops=3D1) Index Cond: (szallitasba =3D 1504) Filter: (sztornozott_pakolas IS NULL) Total runtime: 297.604 ms (24 rows) %----------------------- cut here -----------------------%