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 171949DCAAC for ; Mon, 5 Dec 2005 11:40:37 -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 76081-05 for ; Mon, 5 Dec 2005 11:40:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpgw.computec.de (smtpgw.computec.de [212.123.108.8]) by postgresql.org (Postfix) with ESMTP id B1BE49DCAA8 for ; Mon, 5 Dec 2005 11:40:34 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtpgw.computec.de (Postfix) with ESMTP id BAAE7559DF; Mon, 5 Dec 2005 16:40:39 +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 11803-01-63; Mon, 5 Dec 2005 16:40:39 +0100 (CET) Received: from HERMES.computec.de (unknown [192.168.0.22]) by smtpgw.computec.de (Postfix) with ESMTP id 29AF7559DE; Mon, 5 Dec 2005 16:40:39 +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: Mon, 5 Dec 2005 16:40:38 +0100 Message-ID: <28011CD60FB1724DBA4442E38277F6264A6C43@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: AcX5rhqelz8jkDwsSIeszzJIp1ZFrwAAGHxA 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.199 required=5 tests=[AWL=-0.720, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.199 X-Spam-Level: * X-Archive-Number: 200512/126 X-Sequence-Number: 15947 > -----Urspr=FCngliche Nachricht----- > Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 > Gesendet: Montag, 5. Dezember 2005 16:12 > An: Markus Wollny > Cc: pgsql-performance@postgresql.org > Betreff: Re: AW: AW: [PERFORM] Queries taking ages in PG 8.1,=20 > have been much faster in PG<=3D8.0=20 >=20 > "Markus Wollny" writes: > >> Could we see the pg_stats row for answer.session_id in=20 > both 8.0 and=20 > >> 8.1? >=20 > > Here you are: >=20 > > 8.1: > > Correlation -0.0736492 >=20 > > 8.0.3: > > Correlation -0.237136 >=20 > Interesting --- if the 8.1 database is a dump and restore of=20 > the 8.0, you'd expect the physical ordering to be similar. =20 I dumped the data from my 8.0.1 cluster on 2005-11-18 00:23 using = pg_dumpall with no further options; the dump was passed through iconv to = clear up some UTF-8 encoding issues, then restored into a fresh 8.1 = cluster where it went productive; I used the very same dump to restore = the 8.0.3 cluster. So there is a difference between the two datasets, an = additional 230.328 rows in the answers-table. > Why is 8.1 showing a significantly lower correlation? That=20 > has considerable impact on the estimated cost of an indexscan=20 > (plain not bitmap), and so it might explain why 8.1 is=20 > mistakenly avoiding the indexscan ... I just ran a vacuum analyze on the table, just to make sure that the = stats are up to date (forgot that on the previous run, thanks to = pg_autovacuum...), and the current correlation on the 8.1 installation = is now calculated as -0.158921. That's still more than twice the value = as for the 8.0-db. I don't know whether that is significant, though. Kind regards Markus