Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 - Mailing list pgsql-performance

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
restoredthe 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
got173,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,248rows 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
wouldbe 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
thatseemed 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
thedocs 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_costeven further? 

Kind regards

   Markus