X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 505F39DD6A4 for ; Thu, 1 Dec 2005 12:30:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70420-02 for ; Thu, 1 Dec 2005 12:30:46 -0400 (AST) X-Greylist: delayed 00:41:32.138832 by SQLgrey- Received: from smtpgw.computec.de (smtpgw.computec.de [212.123.108.8]) by postgresql.org (Postfix) with ESMTP id D09219DCB97 for ; Thu, 1 Dec 2005 12:30:44 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtpgw.computec.de (Postfix) with ESMTP id 3FA4D559C9; Thu, 1 Dec 2005 17:30:44 +0100 (CET) Received: from smtpgw.computec.de ([127.0.0.1]) by localhost (localhost [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 16873-01-57; Thu, 1 Dec 2005 17:30:43 +0100 (CET) Received: from HERMES.computec.de (unknown [192.168.0.22]) by smtpgw.computec.de (Postfix) with ESMTP id 85FCA559BE; Thu, 1 Dec 2005 17:30:43 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 Date: Thu, 1 Dec 2005 17:30:42 +0100 Message-ID: <28011CD60FB1724DBA4442E38277F6264A62C1@hermes.computec.de> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Queries taking ages in PG 8.1, have been much faster in PG<=8.0 Thread-Index: AcX2k7l2OJhayaaZTCq12MkgkeC3IwAAI0qw From: "Markus Wollny" To: "Tom Lane" Cc: X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at computec.de X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.439 required=5 tests=[AWL=-0.480, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.439 X-Spam-Level: * X-Archive-Number: 200512/23 X-Sequence-Number: 15844 > -----Urspr=FCngliche Nachricht----- > Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 > Gesendet: Donnerstag, 1. Dezember 2005 17:26 > An: Markus Wollny > Cc: pgsql-performance@postgresql.org > Betreff: Re: [PERFORM] Queries taking ages in PG 8.1, have=20 > been much faster in PG<=3D8.0=20 =20 > It looks like "set enable_nestloop =3D 0" might be a workable=20 > hack for the immediate need. =20 Whow - that works miracles :) "Sort (cost=3D81813.13..81813.14 rows=3D3 width=3D16) (actual = time=3D7526.745..7526.751 rows=3D3 loops=3D1)" " Sort Key: source."position"" " -> HashAggregate (cost=3D81813.07..81813.11 rows=3D3 width=3D16) = (actual time=3D7526.590..7526.601 rows=3D3 loops=3D1)" " -> Merge Join (cost=3D81811.40..81813.03 rows=3D5 width=3D16) = (actual time=3D7423.289..7479.175 rows=3D9806 loops=3D1)" " Merge Cond: ("outer".id =3D "inner".value)" " -> Sort (cost=3D1.05..1.06 rows=3D3 width=3D14) (actual = time=3D0.085..0.091 rows=3D3 loops=3D1)" " Sort Key: source.id" " -> Seq Scan on handy_java source = (cost=3D0.00..1.03 rows=3D3 width=3D14) (actual time=3D0.039..0.049 = rows=3D3 loops=3D1)" " -> Sort (cost=3D81810.35..81811.81 rows=3D583 = width=3D8) (actual time=3D7423.179..7440.062 rows=3D9806 loops=3D1)" " Sort Key: mafo.answer.value" " -> Hash Join (cost=3D27164.31..81783.57 = rows=3D583 width=3D8) (actual time=3D6757.521..7360.822 rows=3D9806 = loops=3D1)" " Hash Cond: ("outer".session_id =3D = "inner".session_id)" " -> Bitmap Heap Scan on answer = (cost=3D506.17..54677.92 rows=3D88334 width=3D8) (actual = time=3D379.245..2660.344 rows=3D162809 loops=3D1)" " Recheck Cond: (question_id =3D 16)" " -> Bitmap Index Scan on = idx_answer_question_id (cost=3D0.00..506.17 rows=3D88334 width=3D0) = (actual time=3D274.632..274.632 rows=3D162814 loops=3D1)" " Index Cond: (question_id =3D 16)" " -> Hash (cost=3D26655.21..26655.21 = rows=3D1175 width=3D8) (actual time=3D3831.362..3831.362 rows=3D9806 = loops=3D1)" " -> Hash Join = (cost=3D4829.33..26655.21 rows=3D1175 width=3D8) (actual = time=3D542.227..3800.985 rows=3D9806 loops=3D1)" " Hash Cond: ("outer".session_id = =3D "inner".session_id)" " -> Bitmap Heap Scan on answer = (cost=3D182.84..21429.34 rows=3D20641 width=3D4) (actual = time=3D292.067..2750.376 rows=3D165762 loops=3D1)" " Recheck Cond: ((question_id = =3D 6) AND (value =3D 1))" " -> Bitmap Index Scan on = idx02_performance (cost=3D0.00..182.84 rows=3D20641 width=3D0) (actual = time=3D167.306..167.306 rows=3D165769 loops=3D1)" " Index Cond: = ((question_id =3D 6) AND (value =3D 1))" " -> Hash = (cost=3D4621.13..4621.13 rows=3D10141 width=3D4) (actual = time=3D182.842..182.842 rows=3D11134 loops=3D1)" " -> Index Scan using = idx01_perf_0005 on participant (cost=3D0.01..4621.13 rows=3D10141 = width=3D4) (actual time=3D0.632..136.126 rows=3D11134 loops=3D1)" " Index Cond: = (date_trunc('month'::text, created) =3D date_trunc('month'::text, (now() = - '1 mon'::interval)))" " Filter: (status =3D = 1)" "Total runtime: 7535.398 ms" > Once you're not under deadline,=20 > I'd like to investigate more closely to find out why 8.1 does=20 > worse than 8.0 here. Please tell me what I can do to help in clearing up this issue, I'd be = very happy to help! Heck, I am happy anyway that there's such a quick = fix, even if it's not a beautiful one :) Kind regards Markus