SQLite: Check-in [51bfd63b]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Disable the table selection rule that tried to prevent full table scans from migrating to the outer loop unless they were optimal. The new scaling of outer-loop costs by cost of inner loops obviates the need for that step. And, in fact, that step causes problems with the new inner-loop cost accounting.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| inner-loop-cost
Files:	files \| file ages \| folders
SHA1:	51bfd63b7f9fe53831570ad124c932cb3582b104
User & Date:	drh 2012-11-09 18:22:26

Context

2012-11-09
18:32		Take into account the cost of inner loops when selecting which table of a join to use for the next outer loop. (check-in: 3f87f459 user: drh tags: trunk)
18:22		Disable the table selection rule that tried to prevent full table scans from migrating to the outer loop unless they were optimal. The new scaling of outer-loop costs by cost of inner loops obviates the need for that step. And, in fact, that step causes problems with the new inner-loop cost accounting. (Closed-Leaf check-in: 51bfd63b user: drh tags: inner-loop-cost)
17:59		Try to take into account the cost of inner loops when selecting which table of a join to use for the outer loop. (check-in: 94255634 user: drh tags: inner-loop-cost)

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4740   4740         }
  4741   4741       }
  4742   4742       whereClauseClear(pWInfo->pWC);
  4743   4743       sqlite3DbFree(db, pWInfo);
  4744   4744     }
  4745   4745   }
  4746   4746   
  4747         -/*
  4748         -** Return TRUE if the wsFlags indicate that a full table scan (or a
  4749         -** full scan of a covering index) is indicated.
  4750         -*/
  4751         -static int isFullscan(unsigned wsFlags){
  4752         -  if( wsFlags & WHERE_COVER_SCAN ) return 1;
  4753         -  if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ) return 1;
  4754         -  return 0;
  4755         -}
  4756         -
  4757   4747   
  4758   4748   /*
  4759   4749   ** Generate the beginning of the loop used for WHERE clause processing.
  4760   4750   ** The return value is a pointer to an opaque structure that contains
  4761   4751   ** information needed to terminate the loop.  Later, the calling routine
  4762   4752   ** should invoke sqlite3WhereEnd() with the return value of this function
  4763   4753   ** in order to complete the WHERE clause processing.
................................................................................
  5128   5118   
  5129   5119           /* Conditions under which this table becomes the best so far:
  5130   5120           **
  5131   5121           **   (1) The table must not depend on other tables that have not
  5132   5122           **       yet run.  (In other words, it must not depend on tables
  5133   5123           **       in inner loops.)
  5134   5124           **
  5135         -        **   (2) A full-table-scan plan cannot supercede indexed plan unless
  5136         -        **       the full-table-scan is an "optimal" plan as defined above.
         5125  +        **   (2) (This rule was removed on 2012-11-09.  The scaling of the
         5126  +        **       cost using the optimal scan cost made this rule obsolete.)
  5137   5127           **
  5138   5128           **   (3) All tables have an INDEXED BY clause or this table lacks an
  5139   5129           **       INDEXED BY clause or this table uses the specific
  5140   5130           **       index specified by its INDEXED BY clause.  This rule ensures
  5141   5131           **       that a best-so-far is always selected even if an impossible
  5142   5132           **       combination of INDEXED BY clauses are given.  The error
  5143   5133           **       will be detected and relayed back to the application later.
................................................................................
  5144   5134           **       The NEVER() comes about because rule (2) above prevents
  5145   5135           **       An indexable full-table-scan from reaching rule (3).
  5146   5136           **
  5147   5137           **   (4) The plan cost must be lower than prior plans, where "cost"
  5148   5138           **       is defined by the compareCost() function above. 
  5149   5139           */
  5150   5140           if( (sWBI.cost.used&sWBI.notValid)==0                    /* (1) */
  5151         -            && (bestJ<0 || (notIndexed&m)!=0                     /* (2) */
  5152         -                || isFullscan(bestPlan.plan.wsFlags)
  5153         -                || !isFullscan(sWBI.cost.plan.wsFlags))
  5154   5141               && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
  5155   5142                   || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
  5156   5143               && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan))   /* (4) */
  5157   5144           ){
  5158   5145             WHERETRACE(("   === table %d (%s) is best so far\n"
  5159   5146                         "       cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n",
  5160   5147                         j, sWBI.pSrc->pTab->zName,