From: Markus Wollny <Markus.Wollny@computec.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
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> (raw)
> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 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,
> have been much faster in PG<=8.0
>
> "Markus Wollny" <Markus.Wollny@computec.de> writes:
> >> Could we see the pg_stats row for answer.session_id in
> both 8.0 and
> >> 8.1?
>
> > Here you are:
>
> > 8.1:
> > Correlation -0.0736492
>
> > 8.0.3:
> > Correlation -0.237136
>
> Interesting --- if the 8.1 database is a dump and restore of
> the 8.0, you'd expect the physical ordering to be similar.
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
> has considerable impact on the estimated cost of an indexscan
> (plain not bitmap), and so it might explain why 8.1 is
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Markus.Wollny@computec.de, tgl@sss.pgh.pa.us
Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0
In-Reply-To: <28011CD60FB1724DBA4442E38277F6264A6C43@hermes.computec.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox