Improve usage of effective_cache_size parameter by assuming that all the

tables in the query compete for cache space, not just the one we are
currently costing an indexscan for.  This seems more realistic, and it
definitely will help in examples recently exhibited by Stefan
Kaltenbrunner.  To get the total size of all the tables involved, we must
tweak the handling of 'append relations' a bit --- formerly we looked up
information about the child tables on-the-fly during set_append_rel_pathlist,
but it needs to be done before we start doing any cost estimation, so
push it into the add_base_rels_to_query scan.

1 files changed, 37 insertions, 2 deletions

diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c
index c8d43a54657..ae44e2bc35c 100644
--- a/src/backend/optimizer/plan/planmain.c
+++ b/src/backend/optimizer/plan/planmain.c
@@ -14,7 +14,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/planmain.c,v 1.95 2006/07/14 14:52:20 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/planmain.c,v 1.96 2006/09/19 22:49:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -85,6 +85,8 @@ query_planner(PlannerInfo *root, List *tlist, double tuple_fraction,
 	RelOptInfo *final_rel;
 	Path	   *cheapestpath;
 	Path	   *sortedpath;
+	Index		rti;
+	double		total_pages;
 
 	/* Make tuple_fraction accessible to lower-level routines */
 	root->tuple_fraction = tuple_fraction;
@@ -122,11 +124,44 @@ query_planner(PlannerInfo *root, List *tlist, double tuple_fraction,
 	root->oj_info_list = NIL;
 
 	/*
-	 * Construct RelOptInfo nodes for all base relations in query.
+	 * Construct RelOptInfo nodes for all base relations in query, and
+	 * indirectly for all appendrel member relations ("other rels").  This
+	 * will give us a RelOptInfo for every "simple" (non-join) rel involved
+	 * in the query.
+	 *
+	 * Note: the reason we find the rels by searching the jointree and
+	 * appendrel list, rather than just scanning the rangetable, is that the
+	 * rangetable may contain RTEs for rels not actively part of the query,
+	 * for example views.  We don't want to make RelOptInfos for them.
 	 */
 	add_base_rels_to_query(root, (Node *) parse->jointree);
 
 	/*
+	 * We should now have size estimates for every actual table involved
+	 * in the query, so we can compute total_table_pages.  Note that
+	 * appendrels are not double-counted here, even though we don't bother
+	 * to distinguish RelOptInfos for appendrel parents, because the parents
+	 * will still have size zero.
+	 *
+	 * XXX if a table is self-joined, we will count it once per appearance,
+	 * which perhaps is the wrong thing ... but that's not completely clear,
+	 * and detecting self-joins here is difficult, so ignore it for now.
+	 */
+	total_pages = 0;
+	for (rti = 1; rti < root->simple_rel_array_size; rti++)
+	{
+		RelOptInfo *brel = root->simple_rel_array[rti];
+
+		if (brel == NULL)
+			continue;
+
+		Assert(brel->relid == rti); /* sanity check on array */
+
+		total_pages += (double) brel->pages;
+	}
+	root->total_table_pages = total_pages;
+
+	/*
 	 * Examine the targetlist and qualifications, adding entries to baserel
 	 * targetlists for all referenced Vars.  Restrict and join clauses are
 	 * added to appropriate lists belonging to the mentioned relations.  We