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 11:28:54 +0100
Message-ID: <28011CD60FB1724DBA4442E38277F6264A69CD@hermes.computec.de> (raw)
Hi!
> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 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
> been much faster in PG<=8.0
> The data is not quite the same, right? I notice different
> numbers of rows being returned.
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,
> rather than directly on the fetched tuple, is a net loss
> here. It looks like 8.1 would have made the right plan
> choice if it had made a better estimate of the combined
> selectivity of the question_id and value conditions, so
> ultimately this is another manifestation of the lack of
> cross-column statistics. What I find interesting though is
> that the plain index scan in 8.0 is so enormously cheaper
> than it's estimated to be. Perhaps the answer table in your
> 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?
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
> 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
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: <28011CD60FB1724DBA4442E38277F6264A69CD@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