Skip site navigation (1) Skip section navigation (2)

Re: View performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: View performance
Date: 2002-12-26 23:43:39
Message-ID: 20650.1040946219@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
I wrote:
>> This seems to indicate some estimation problems in cost_hashjoin; the
>> estimated cost for the hashjoin is evidently a lot higher than it should
>> be.

The answer is that estimate_hash_bucketsize() is producing a rather
silly result in this situation, viz. a bucketsize "fraction" that's well
above 1.0.  I've applied the following band-aid patch to CVS tip, which
perhaps you might like to use locally.  But probably the long-range
answer is to rethink what that routine is doing --- its adjustment for
skewed data distributions is perhaps not such a great idea.

			regards, tom lane


*** src/backend/optimizer/path/costsize.c.orig	Fri Dec 13 19:17:55 2002
--- src/backend/optimizer/path/costsize.c	Thu Dec 26 18:34:02 2002
***************
*** 1164,1169 ****
--- 1164,1179 ----
  	if (avgfreq > 0.0 && mcvfreq > avgfreq)
  		estfract *= mcvfreq / avgfreq;
  
+ 	/*
+ 	 * Clamp bucketsize to sane range (the above adjustment could easily
+ 	 * produce an out-of-range result).  We set the lower bound a little
+ 	 * above zero, since zero isn't a very sane result.
+ 	 */
+ 	if (estfract < 1.0e-6)
+ 		estfract = 1.0e-6;
+ 	else if (estfract > 1.0)
+ 		estfract = 1.0;
+ 
  	ReleaseSysCache(tuple);
  
  	return (Selectivity) estfract;

In response to

pgsql-performance by date

Next:From: yutaka_inadaDate: 2002-12-27 07:03:48
Subject: executing pgsql on Xeon-dual machine
Previous:From: Tom LaneDate: 2002-12-26 20:45:55
Subject: Re: View performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group