Thread

1. Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0

   Markus Wollny <markus.wollny@computec.de> — 2005-12-05T14:44:54Z

    
   
   > -----Ursprüngliche Nachricht-----
   > Von: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
   > Gesendet: Montag, 5. Dezember 2005 15:33
   > An: Markus Wollny
   > Cc: pgsql-performance@postgresql.org
   > Betreff: Re: AW: [PERFORM] Queries taking ages in PG 8.1, 
   > have been much faster in PG<=8.0 
    
   > Could we see the pg_stats row for answer.session_id in both 
   > 8.0 and 8.1?
   
   Here you are:
   
   select null_frac
   	, avg_width
   	, n_distinct
   	, most_common_vals
   	, most_common_freqs
   	, histogram_bounds
   	, Correlation
   from pg_stats
   where schemaname = 'survey'
   and tablename = 'answer'
   and attname = 'session_id';
   
   8.1:
   null_frac		0
   avg_width		4
   n_distinct		33513
   most_common_vals	{1013854,1017890,1021551,1098817,764249,766938,776353,780954,782232,785985}
   most_common_freqs	{0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
   histogram_bounds	{757532,819803,874935,938170,1014421,1081507,1164659,1237281,1288267,1331016,1368939}
   Correlation		-0.0736492
   
   8.0.3:
   null_frac		0
   avg_width		4
   n_distinct		29287
   most_common_vals	{765411,931762,983933,1180453,1181959,1229963,1280249,1288736,1314970,764901}
   most_common_freqs	{0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.000666667}
   histogram_bounds	{757339,822949,875834,939085,1004782,1065251,1140682,1218336,1270024,1312170,1353082}
   Correlation		-0.237136
   
   Kind regards
   
      Markus
   
   

2. Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-05T15:11:41Z

   "Markus Wollny" <Markus.Wollny@computec.de> 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.  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 ...
   
   			regards, tom lane
   
   

3. Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0

   Manfred Koizar <mkoi-pg@aon.at> — 2005-12-10T17:14:00Z

   On Mon, 05 Dec 2005 10:11:41 -0500, Tom Lane <tgl@sss.pgh.pa.us>
   wrote:
   >> Correlation		-0.0736492
   >> Correlation		-0.237136
   
   >That has considerable impact on the
   >estimated cost of an indexscan
   
   The cost estimator uses correlationsquared.  So all correlations
   between -0.3 and +0.3 can be considered equal under the assumption
   that estimation errors of up to 10% are acceptable.
   Servus
    Manfred