> -----Ursprüngliche Nachricht----- > Von: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Gesendet: Montag, 5. Dezember 2005 16:12 > An: Markus Wollny > Cc: pgsql-performance@xxxxxxxxxxxxxx > Betreff: Re: AW: AW: [PERFORM] Queries taking ages in PG 8.1, > have been much faster in PG<=8.0 > > "Markus Wollny" <Markus.Wollny@xxxxxxxxxxx> 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
