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 11:28:54 +0100
Message-ID: <28011CD60FB1724DBA4442E38277F6264A69CD@hermes.computec.de>
Thread-Topic: [PERFORM] Queries taking ages in PG 8.1,
	have been much faster in PG<=8.0
Thread-Index: AcX5AN3qBuO5QRTdSqCDKp1mHFnW2gAgG1/A
From: "Markus Wollny" <Markus.Wollny@computec.de>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>

Hi!=20

> -----Urspr=FCngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
> Gesendet: Sonntag, 4. Dezember 2005 19:32
> 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

> The data is not quite the same, right?  I notice different=20
> numbers of rows being returned. =20

No, you're right, I didn't manage to restore the 8.1 dump into the 8.0.3 =
cluster, so I took the quick route and restored the last dump from my =
8.0 installation. The numbers should be roughly within the same range, =
though:

Table answer has got 8,646,320 rows (counted and estimated, as this db =
is not live, obviously), table participant has got 173,998 rows; for =
comparison:
The production db had an estimated 8,872,130, counted 8,876,648 rows for =
table answer, and estimated 178,165, counted 178,248 rows for =
participant. As the numbers are a mere 2% apart, I should think that =
this wouldn't make that much difference.

> It seems that checking question_id/value via the index,=20
> rather than directly on the fetched tuple, is a net loss=20
> here.  It looks like 8.1 would have made the right plan=20
> choice if it had made a better estimate of the combined=20
> selectivity of the question_id and value conditions, so=20
> ultimately this is another manifestation of the lack of=20
> cross-column statistics.  What I find interesting though is=20
> that the plain index scan in 8.0 is so enormously cheaper=20
> than it's estimated to be.  Perhaps the answer table in your=20
> 8.0 installation is almost perfectly ordered by session_id?

Not quite - there may be several concurrent sessions at any one time, =
but ordinarily the answers for one session-id would be quite close =
together, in a lot of cases even in perfect sequence, so "almost =
perfectly" might be a fair description, depending on the exact =
definition of "almost" :)

> Are you using default values for the planner cost parameters?=20

I have to admit that I did tune the random_page_cost and =
effective_cache_size settings ages ago (7.1-ish) to a value that seemed =
to work best then - and didn't touch it ever since, although my data =
pool has grown quite a bit over time. cpu_tuple_cost, =
cpu_index_tuple_cost and cpu_operator_cost are using default values.

>  It looks like reducing random_page_cost would help bring the=20
> planner estimates into line with reality on your machines.

I had set random_page_cost to 1.4 already, so I doubt that it would do =
much good to further reduce the value - reading the docs and the =
suggestions for tuning I would have thought that I should actually =
consider increasing this value a bit, as not all of my data will fit in =
memory any more. Do you nevertheless want me to try what happens if I =
reduce random_page_cost even further?

Kind regards

   Markus