Hi!
> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:[EMAIL PROTECTED]
> Gesendet: Sonntag, 4. Dezember 2005 19:32
> An: Markus Wollny
> Cc: [email protected]
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
