To: "Dave Dutcher" <dave@tridecap.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Bad Row Count Estimate on View with 8.2 
In-reply-to: <000401c742fe$0e24fd20$8300a8c0@tridecap.com> 
References: <000401c742fe$0e24fd20$8300a8c0@tridecap.com>
Comments: In-reply-to "Dave Dutcher" <dave@tridecap.com>
	message dated "Sun, 28 Jan 2007 11:02:12 -0600"
Date: Sun, 28 Jan 2007 13:47:58 -0500
Message-ID: <17556.1170010078@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Dave Dutcher" <dave@tridecap.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.)