Date:

Description

There were two bugs in the optimizer code that was causing bizarre behaviour. One of the bugs made the plans sensitive to the order in which relations are specified and the other caused the size of the output of sequential scans with restrictions to be "0".

Thanks to Sunita Sarawagi (sunita@cs.berkeley.edu) for finding and fixing the bugs.

Fix

Apply this patch.

===================================================================
RCS file: /usr/local/devel/pglite/cvs/src/RELEASE.patchlevel,v
retrieving revision 1.4
diff -c -r1.4 RELEASE.patchlevel
*** 1.4	1995/10/31 07:52:11
--- RELEASE.patchlevel	1995/10/31 08:00:34
***************
*** 3,10 ****
  ## This file is used to maintain sequencing of patches
  
  version:	1.0
! patch level:	13
! patch date:	Mon Oct 30 23:42:24 PST 1995
  
  
  
--- 3,10 ----
  ## This file is used to maintain sequencing of patches
  
  version:	1.0
! patch level:	14
! patch date:	Mon Oct 30 23:52:26 PST 1995
  
  
  
===================================================================
RCS file: /usr/local/devel/pglite/cvs/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.10
diff -c -r1.10 allpaths.c
*** 1.10	1995/03/17 20:26:06
--- src/backend/optimizer/path/allpaths.c	1995/10/31 06:55:52
***************
*** 121,127 ****
  	while(lnext(lastpath)!=NIL)
  	    lastpath=lnext(lastpath);
  	prune_rel_path(rel, (Path*)lfirst(lastpath));
! 
  	rel->size = compute_rel_size(rel);
  	rel->width = compute_rel_width(rel);
      }
--- 121,131 ----
  	while(lnext(lastpath)!=NIL)
  	    lastpath=lnext(lastpath);
  	prune_rel_path(rel, (Path*)lfirst(lastpath));
!       /*
!        * if there is a qualification of sequential scan the selec.
!        * value is not set -- so set it explicitly -- Sunita
!        */
!       set_rest_selec(root, rel->clauseinfo);
  	rel->size = compute_rel_size(rel);
  	rel->width = compute_rel_width(rel);
      }
===================================================================
RCS file: /usr/local/devel/pglite/cvs/src/backend/optimizer/path/clausesel.c,v
retrieving revision 1.8
diff -c -r1.8 clausesel.c
*** 1.8	1995/06/12 22:37:21
--- src/backend/optimizer/path/clausesel.c	1995/10/31 06:55:59
***************
*** 31,37 ****
  #include "utils/elog.h"
  #include "utils/lsyscache.h"
  
! static void set_rest_selec(Query *root,List *clauseinfo_list);
  static Cost compute_selec(Query *root, List *clauses, List *or_selectivities);
  /* static Oid translate_relid(int relid); */
  
--- 31,37 ----
  #include "utils/elog.h"
  #include "utils/lsyscache.h"
  
! void set_rest_selec(Query *root,List *clauseinfo_list);
  static Cost compute_selec(Query *root, List *clauses, List *or_selectivities);
  /* static Oid translate_relid(int relid); */
  
***************
*** 115,121 ****
   * Returns nothing of interest.
   *    
   */
! static void
  set_rest_selec(Query *root, List *clauseinfo_list)
  {
      List *temp = NIL;
--- 115,121 ----
   * Returns nothing of interest.
   *    
   */
! void
  set_rest_selec(Query *root, List *clauseinfo_list)
  {
      List *temp = NIL;
***************
*** 164,170 ****
  	/*
  	 * Boolean variables get a selectivity of 1/2.
  	 */
! 	return(0.5);
      } else if (not_clause (clause)) {
  	/*
  	 * 'not' gets "1.0 - selectivity-of-inner-clause".
--- 164,170 ----
  	/*
  	 * Boolean variables get a selectivity of 1/2.
  	 */
! 	return(0.1);
      } else if (not_clause (clause)) {
  	/*
  	 * 'not' gets "1.0 - selectivity-of-inner-clause".
===================================================================
RCS file: /usr/local/devel/pglite/cvs/src/backend/optimizer/util/pathnode.c,v
retrieving revision 1.9
diff -c -r1.9 pathnode.c
*** 1.9	1995/07/04 02:25:47
--- src/backend/optimizer/util/pathnode.c	1995/10/31 06:56:07
***************
*** 118,124 ****
  	    unique_paths = lcons(new_path, unique_paths);
  	} else if (old_path==NULL) {
  	    ;	/* do nothing if path is not cheaper */
! 	} else if (IsA(old_path,Path)) {
  	    new_path->parent = parent_rel;
  	    if (!parent_rel->pruneable) {
  		unique_paths = lcons(new_path, unique_paths);
--- 118,124 ----
  	    unique_paths = lcons(new_path, unique_paths);
  	} else if (old_path==NULL) {
  	    ;	/* do nothing if path is not cheaper */
! 	} else if (old_path != NULL) { /* (IsA(old_path,Path)) { */
  	    new_path->parent = parent_rel;
  	    if (!parent_rel->pruneable) {
  		unique_paths = lcons(new_path, unique_paths);