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 6DFC09DCBCE for ; Sun, 4 Dec 2005 09:25:22 -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 47393-02 for ; Sun, 4 Dec 2005 09:25:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id EF9DD9DCB8C for ; Sun, 4 Dec 2005 09:25:17 -0400 (AST) Received: from smtpgw.computec.de (unknown [212.123.108.8]) by svr2.postgresql.org (Postfix) with ESMTP id 1A0AAF0BA4 for ; Sun, 4 Dec 2005 13:25:20 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtpgw.computec.de (Postfix) with ESMTP id 46D81559AD; Sun, 4 Dec 2005 14:25:17 +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 17106-02-35; Sun, 4 Dec 2005 14:25:16 +0100 (CET) Received: from HERMES.computec.de (unknown [192.168.0.22]) by smtpgw.computec.de (Postfix) with ESMTP id 7CED6559A7; Sun, 4 Dec 2005 14:25:16 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5F8D6.12F298F4" Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 Date: Sun, 4 Dec 2005 14:24:37 +0100 Message-ID: <28011CD60FB1724DBA4442E38277F626114E3A@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: AcX2k7l2OJhayaaZTCq12MkgkeC3IwAAI0qwAJA1mcI= References: <18959924631B864DB8D5268D81D746A90CCE2DD9@hermes.computec.de> From: "Markus Wollny" To: "Markus Wollny" , "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.146 required=5 tests=[AWL=-0.187, HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.146 X-Spam-Level: * X-Archive-Number: 200512/115 X-Sequence-Number: 15936 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5F8D6.12F298F4 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi! > -----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 > > 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. I've just set up a PostgreSQL 8.0.3 installation ... select version(); version -------------------------------------------------------------------------= ------------------- PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 = (Debian 1:3.3.5-13) (1 row) ...and restored a dump there; here's the explain analyze of the query = for 8.0.3: = QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= ----------------- Sort (cost=3D5193.63..5193.63 rows=3D3 width=3D16) (actual = time=3D7365.107..7365.110 rows=3D3 loops=3D1) Sort Key: source."position" -> HashAggregate (cost=3D5193.59..5193.60 rows=3D3 width=3D16) = (actual time=3D7365.034..7365.041 rows=3D3 loops=3D1) -> Nested Loop (cost=3D0.00..5193.57 rows=3D3 width=3D16) = (actual time=3D3190.642..7300.820 rows=3D11086 loops=3D1) -> Nested Loop (cost=3D0.00..3602.44 rows=3D4 = width=3D20) (actual time=3D3169.968..5875.153 rows=3D11087 loops=3D1) -> Nested Loop (cost=3D0.00..1077.95 rows=3D750 = width=3D16) (actual time=3D36.599..2778.129 rows=3D158288 loops=3D1) -> Seq Scan on handy_java source = (cost=3D0.00..1.03 rows=3D3 width=3D14) (actual time=3D6.503..6.514 = rows=3D3 loops=3D1) -> Index Scan using idx02_performance on = answer (cost=3D0.00..355.85 rows=3D250 width=3D8) (actual = time=3D10.071..732.746 rows=3D52763 loops=3D3) Index Cond: ((answer.question_id =3D = 16) AND (answer.value =3D "outer".id)) -> Index Scan using pk_participant on participant = (cost=3D0.00..3.35 rows=3D1 width=3D4) (actual time=3D0.016..0.016 = rows=3D0 loops=3D158288) Index Cond: (participant.session_id =3D = "outer".session_id) Filter: ((status =3D 1) AND = (date_trunc('month'::text, created) =3D date_trunc('month'::text, (now() = - '2 mons'::interval)))) -> Index Scan using idx_answer_session_id on answer = (cost=3D0.00..397.77 rows=3D1 width=3D4) (actual time=3D0.080..0.122 = rows=3D1 loops=3D11087) Index Cond: ("outer".session_id =3D = answer.session_id) Filter: ((question_id =3D 6) AND (value =3D 1)) Total runtime: 7365.461 ms (16 rows) Does this tell you anything useful? It's not on the same machine, mind = you, but configuration for PostgreSQL is absolutely identical (apart = from the autovacuum-lines which 8.0.3 doesn't like). Kind regards Markus ------_=_NextPart_001_01C5F8D6.12F298F4 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [PERFORM] Queries taking ages in PG 8.1, have been much = faster in PG<=3D8.0

Hi!

> -----Urspr=FCngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 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
> been much faster in PG<=3D8.0

> It looks like "set enable_nestloop =3D 0" might be a = workable
> hack for the immediate need. 
>
> Once you're not under deadline,
> I'd like to investigate more closely to find out why 8.1 does
> worse than 8.0 here.


I've just set up a PostgreSQL 8.0.3 installation ...

select version();
            &= nbsp;           &n= bsp;           &nb= sp;     version
-------------------------------------------------------------------------= -------------------
 PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) = 3.3.5 (Debian 1:3.3.5-13)
(1 row)

...and restored a dump there; here's the explain analyze of the query = for 8.0.3:

            &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =    QUERY PLAN
-------------------------------------------------------------------------= -------------------------------------------------------------------------= -----------------
 Sort  (cost=3D5193.63..5193.63 rows=3D3 width=3D16) (actual = time=3D7365.107..7365.110 rows=3D3 loops=3D1)
   Sort Key: source."position"
   ->  HashAggregate  (cost=3D5193.59..5193.60 = rows=3D3 width=3D16) (actual time=3D7365.034..7365.041 rows=3D3 = loops=3D1)
         ->  Nested = Loop  (cost=3D0.00..5193.57 rows=3D3 width=3D16) (actual = time=3D3190.642..7300.820 rows=3D11086 loops=3D1)
            &= nbsp;  ->  Nested Loop  (cost=3D0.00..3602.44 rows=3D4 = width=3D20) (actual time=3D3169.968..5875.153 rows=3D11087 = loops=3D1)
            &= nbsp;        ->  Nested = Loop  (cost=3D0.00..1077.95 rows=3D750 width=3D16) (actual = time=3D36.599..2778.129 rows=3D158288 loops=3D1)
            &= nbsp;           &n= bsp;  ->  Seq Scan on handy_java source  = (cost=3D0.00..1.03 rows=3D3 width=3D14) (actual time=3D6.503..6.514 = rows=3D3 loops=3D1)
            &= nbsp;           &n= bsp;  ->  Index Scan using idx02_performance on = answer  (cost=3D0.00..355.85 rows=3D250 width=3D8) (actual = time=3D10.071..732.746 rows=3D52763 loops=3D3)
            &= nbsp;           &n= bsp;        Index Cond: = ((answer.question_id =3D 16) AND (answer.value =3D = "outer".id))
            &= nbsp;        ->  Index Scan = using pk_participant on participant  (cost=3D0.00..3.35 rows=3D1 = width=3D4) (actual time=3D0.016..0.016 rows=3D0 loops=3D158288)
            &= nbsp;           &n= bsp;  Index Cond: (participant.session_id =3D = "outer".session_id)
            &= nbsp;           &n= bsp;  Filter: ((status =3D 1) AND (date_trunc('month'::text, = created) =3D date_trunc('month'::text, (now() - '2 = mons'::interval))))
            &= nbsp;  ->  Index Scan using idx_answer_session_id on = answer  (cost=3D0.00..397.77 rows=3D1 width=3D4) (actual = time=3D0.080..0.122 rows=3D1 loops=3D11087)
            &= nbsp;        Index Cond: = ("outer".session_id =3D answer.session_id)
            &= nbsp;        Filter: ((question_id = =3D 6) AND (value =3D 1))
 Total runtime: 7365.461 ms
(16 rows)

Does this tell you anything useful? It's not on the same machine, mind = you, but configuration for PostgreSQL is absolutely identical (apart = from the autovacuum-lines which 8.0.3 doesn't like).

Kind regards

   Markus

------_=_NextPart_001_01C5F8D6.12F298F4--