Re: Why is plan (and performance) different on partitioned table?

I wrote:
> Joins against partitioned tables suck in 8.1 :-(.

Actually ... while the above is a true statement, it's too flippant a
response for your problem. The reason the planner is going for a
mergejoin in your example is that it thinks the mergejoin will terminate
early. (Notice that the cost estimate for the mergejoin is actually
quite a bit less than the estimate for its first input.) This estimate
can only be made if the planner has statistics that say that one of the
join columns has a max value much less than the other's. Well, that's
fine, but where the heck did it get the stats for the partitioned table?
We don't compute union statistics for partitions. The answer is that
it's confused and is using the stats for just the parent table as if
they were representative for the whole inheritance tree.

I think this behavior was intentional back when it was coded, but when
inheritance is being used for partitioning, it's clearly brain-dead.
We should either not assume anything about the statistics for an
inheritance tree, or make a real effort to compute them.

For the moment, I've applied a quick patch that makes sure we don't
assume anything.

If you don't have anything in the parent table br_1min, then deleting
the (presumably obsolete) pg_statistic rows for it should fix your
immediate problem. Otherwise, consider applying the attached.

regards, tom lane

Index: src/backend/optimizer/path/allpaths.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.137.2.2
diff -c -r1.137.2.2 allpaths.c
*** src/backend/optimizer/path/allpaths.c 13 Feb 2006 16:22:29 -0000 1.137.2.2
--- src/backend/optimizer/path/allpaths.c 2 May 2006 04:31:27 -0000
***************
*** 264,269 ****
--- 264,276 ----
errmsg("SELECT FOR UPDATE/SHARE is not supported for inheritance queries")));

/*
+ * We might have looked up indexes for the parent rel, but they're
+ * really not relevant to the appendrel. Reset the pointer to avoid
+ * any confusion.
+ */
+ rel->indexlist = NIL;
+
+ /*
* Initialize to compute size estimates for whole inheritance tree
*/
rel->rows = 0;
Index: src/backend/utils/adt/selfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.191.2.1
diff -c -r1.191.2.1 selfuncs.c
*** src/backend/utils/adt/selfuncs.c 22 Nov 2005 18:23:22 -0000 1.191.2.1
--- src/backend/utils/adt/selfuncs.c 2 May 2006 04:31:27 -0000
***************
*** 2970,2988 ****
(varRelid == 0 || varRelid == ((Var *) basenode)->varno))
{
Var *var = (Var *) basenode;
! Oid relid;

vardata->var = basenode; /* return Var without relabeling */
vardata->rel = find_base_rel(root, var->varno);
vardata->atttype = var->vartype;
vardata->atttypmod = var->vartypmod;

! relid = getrelid(var->varno, root->parse->rtable);

! if (OidIsValid(relid))
{
vardata->statsTuple = SearchSysCache(STATRELATT,
! ObjectIdGetDatum(relid),
Int16GetDatum(var->varattno),
0, 0);
}
--- 2970,2996 ----
(varRelid == 0 || varRelid == ((Var *) basenode)->varno))
{
Var *var = (Var *) basenode;
! RangeTblEntry *rte;

vardata->var = basenode; /* return Var without relabeling */
vardata->rel = find_base_rel(root, var->varno);
vardata->atttype = var->vartype;
vardata->atttypmod = var->vartypmod;

! rte = rt_fetch(var->varno, root->parse->rtable);

! if (rte->inh)
! {
! /*
! * XXX This means the Var represents a column of an append relation.
! * Later add code to look at the member relations and try to derive
! * some kind of combined statistics?
! */
! }
! else if (rte->rtekind == RTE_RELATION)
{
vardata->statsTuple = SearchSysCache(STATRELATT,
! ObjectIdGetDatum(rte->relid),
Int16GetDatum(var->varattno),
0, 0);
}