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-24 07:44:34
Message-ID: 23451.1169624674@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Dave Dutcher" <dave(at)tridecap(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Galy LeeDate: 2007-01-24 07:44:55
Subject: Re: how to plan for vacuum?
Previous:From: Galy LeeDate: 2007-01-24 05:37:44
Subject: how to plan for vacuum?

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