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);
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
