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: Wed, 24 Jan 2007 02:44:34 -0500
Message-ID: <23451.1169624674@sss.pgh.pa.us> (raw)
In-Reply-To: <008e01c73f3b$0e509190$8300a8c0@tridecap.com>
References: <008e01c73f3b$0e509190$8300a8c0@tridecap.com>
"Dave Dutcher" <dave@tridecap.com> writes:
> I discovered a query which is taking 70 seconds on 8.2.1 which used to take
> under a second on 8.1.2. I was digging into what was causing it and I
> believe the problem is a view which the planner estimates will return 1 row
> when it actually returns 3500.
This is evidently a failure of estimate_num_groups(). However, I do not
see any difference in that code between 8.1 and 8.2 branch tips. I do
notice a possibly-relevant change that was applied in 8.1.4:
2006-05-02 00:34 tgl
* src/backend/: optimizer/path/allpaths.c, utils/adt/selfuncs.c
(REL8_1_STABLE): Avoid assuming that statistics for a parent
relation reflect the properties of the union of its child relations
as well. This might have been a good idea when it was originally
coded, but it's a fatally bad idea when inheritance is being used
for partitioning. It's better to have no stats at all than
completely misleading stats. Per report from Mark Liberman.
The bug arguably exists all the way back, but I've only patched
HEAD and 8.1 because we weren't particularly trying to support
partitioning before 8.1.
Eventually we ought to look at deriving union statistics instead of
just punting, but for now the drop kick looks good.
I think this was only meant to apply to table inheritance situations,
but on reflection it might affect UNION queries too. The question is
whether the numbers it was using before really mean anything --- they
seem to have been better-than-nothing in your particular case, but I'm
not sure that translates to a conclusion that we should depend on 'em.
In fact, since there isn't any "parent relation" in a UNION, I'm not
sure that this patch actually changed your results ... but I'm not
seeing what else would've ...
regards, tom lane
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: <23451.1169624674@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