Re: Bad Row Count Estimate on View with 8.2

"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.)