From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dave Dutcher <dave@tridecap.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad Row Count Estimate on View with 8.2
Date: Sun, 28 Jan 2007 13:47:58 -0500
Message-ID: <17556.1170010078@sss.pgh.pa.us> (raw)
In-Reply-To: <000401c742fe$0e24fd20$8300a8c0@tridecap.com>
References: <000401c742fe$0e24fd20$8300a8c0@tridecap.com>
"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
inline patch 1: allpaths.c (+6/-0 lines) download inline-1.patchIndex: 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.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dave@tridecap.com
Subject: Re: Bad Row Count Estimate on View with 8.2
In-Reply-To: <17556.1170010078@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox