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

Re: Bad Row Count Estimate on View with 8.2

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dave Dutcher" <dave(at)tridecap(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bad Row Count Estimate on View with 8.2
Date: 2007-01-28 18:47:58
Message-ID: 17556.1170010078@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Dave Dutcher" <dave(at)tridecap(dot)com> writes:
> Thanks for looking into it.  I thought I might actually test if it was the
> patch you mentioned which changed my results, but I haven't had time.
> Because you mentioned it was grouping on the results of a UNION ALL which
> was throwing off the row estimate I changed my query from a UNION ALL/GROUP
> BY to a GROUP BY/FULL OUTER JOIN.  The view runs a hair slower by itself,
> but the better estimate of rows makes it work much better for joining with.

I took another look and think I found the problem: 8.2's new code for
flattening UNION ALL subqueries into "append relations" is failing to
initialize all the fields of the appendrel, which confuses
estimate_num_groups (and perhaps other places).  I think this will fix
it for you.

			regards, tom lane

Index: allpaths.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.154
diff -c -r1.154 allpaths.c
*** allpaths.c	4 Oct 2006 00:29:53 -0000	1.154
--- allpaths.c	28 Jan 2007 18:44:01 -0000
***************
*** 384,389 ****
--- 384,395 ----
  	}
  
  	/*
+ 	 * Set "raw tuples" count equal to "rows" for the appendrel; needed
+ 	 * because some places assume rel->tuples is valid for any baserel.
+ 	 */
+ 	rel->tuples = rel->rows;
+ 
+ 	/*
  	 * Finally, build Append path and install it as the only access path for
  	 * the parent rel.	(Note: this is correct even if we have zero or one
  	 * live subpath due to constraint exclusion.)

In response to

Responses

pgsql-performance by date

Next:From: Campbell, LanceDate: 2007-01-28 20:21:39
Subject: work-mem
Previous:From: Dave DutcherDate: 2007-01-28 17:02:12
Subject: Re: Bad Row Count Estimate on View with 8.2

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