/ Check-in [cbef38c2]
Login

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

Overview
Comment:Minor performance tuning of the NGQP.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:cbef38c2d123e7d5a02c2a2450e8b329e3e96ee9
User & Date: drh 2013-06-05 16:19:59
Context
2013-06-05
17:53
Performance improvement for the OR-clause analysis in the NGQP. check-in: 9b1c4954 user: drh tags: nextgen-query-plan-exp
16:19
Minor performance tuning of the NGQP. check-in: cbef38c2 user: drh tags: nextgen-query-plan-exp
12:47
Performance tweak to whereLoopInsert(). check-in: 1c4a7880 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/where.c.

748
749
750
751
752
753
754
755

756
757
758
759
760
761
762
763



764
765

766
767
768
769
770
771
772
....
4778
4779
4780
4781
4782
4783
4784
4785
4786
4787
4788
4789
4790
4791
4792
....
4797
4798
4799
4800
4801
4802
4803

4804
4805
4806
4807
4808
4809
4810
4811
4812
4813
4814
4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
  int iCur,               /* Cursor to scan for */
  int iColumn,            /* Column to scan for */
  u32 opMask,             /* Operator(s) to scan for */
  Index *pIdx             /* Must be compatible with this index */
){
  int j;

  memset(pScan, 0, sizeof(*pScan));

  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  if( pIdx && iColumn>=0 ){
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
      if( NEVER(j>=pIdx->nColumn) ) return 0;
    }
    pScan->zCollName = pIdx->azColl[j];



  }
  pScan->opMask = opMask;

  pScan->aEquiv[0] = iCur;
  pScan->aEquiv[1] = iColumn;
  pScan->nEquiv = 2;
  pScan->iEquiv = 2;
  return whereScanNext(pScan);
}

................................................................................
** attempts to find the lowest cost path that visits each WhereLoop
** once.  This path is then loaded into the pWInfo->a[].pWLoop fields.
**
** Return SQLITE_OK on success or SQLITE_NOMEM of a memory allocation
** error occurs.
*/
static int wherePathSolver(WhereInfo *pWInfo, double nRowEst){
  const int mxChoice = 10;  /* Maximum number of simultaneous paths tracked */
  int nLoop;                /* Number of terms in the join */
  sqlite3 *db;              /* The database connection */
  int iLoop;                /* Loop counter over the terms of the join */
  int ii, jj;               /* Loop counters */
  double rCost;             /* Cost of a path */
  double mxCost;            /* Maximum cost of a set of paths */
  double rSortCost;         /* Cost to do a sort */
................................................................................
  WherePath *pTo;           /* An element of aTo[] that we are working on */
  WhereLoop *pWLoop;        /* One of the WhereLoop objects */
  WhereLoop **pX;           /* Used to divy up the pSpace memory */
  char *pSpace;             /* Temporary memory used by this routine */

  db = pWInfo->pParse->db;
  nLoop = pWInfo->nLevel;

  assert( nLoop<=pWInfo->pTabList->nSrc );
#ifdef WHERETRACE_ENABLED
  if( sqlite3WhereTrace>=2 ) sqlite3DebugPrintf("---- begin solver\n");
#endif

  /* Allocate and initialize space for aTo and aFrom */
  ii = (sizeof(WherePath)+sizeof(WhereLoop*)*nLoop)*mxChoice*2;
  pSpace = sqlite3DbMallocRaw(db, ii);
  if( pSpace==0 ) return SQLITE_NOMEM;
  aTo = (WherePath*)pSpace;
  aFrom = aTo+mxChoice;
  memset(aFrom, 0, sizeof(aFrom[0]));
  pX = (WhereLoop**)(aFrom+mxChoice);
  for(ii=0, pFrom=aTo; ii<mxChoice*2; ii++, pFrom++, pX += nLoop){
    pFrom->aLoop = pX;
  }

  /* Seed the search with a single WherePath containing zero WhereLoops */
  aFrom[0].nRow = (double)1;
  nFrom = 1;








|
>








>
>
>


>







 







|







 







>













|







748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
....
4783
4784
4785
4786
4787
4788
4789
4790
4791
4792
4793
4794
4795
4796
4797
....
4802
4803
4804
4805
4806
4807
4808
4809
4810
4811
4812
4813
4814
4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
4828
4829
4830
  int iCur,               /* Cursor to scan for */
  int iColumn,            /* Column to scan for */
  u32 opMask,             /* Operator(s) to scan for */
  Index *pIdx             /* Must be compatible with this index */
){
  int j;

  /* memset(pScan, 0, sizeof(*pScan)); */
  pScan->pCurrent = 0;
  pScan->pOrigWC = pWC;
  pScan->pWC = pWC;
  if( pIdx && iColumn>=0 ){
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
      if( NEVER(j>=pIdx->nColumn) ) return 0;
    }
    pScan->zCollName = pIdx->azColl[j];
  }else{
    pScan->idxaff = 0;
    pScan->zCollName = 0;
  }
  pScan->opMask = opMask;
  pScan->k = 0;
  pScan->aEquiv[0] = iCur;
  pScan->aEquiv[1] = iColumn;
  pScan->nEquiv = 2;
  pScan->iEquiv = 2;
  return whereScanNext(pScan);
}

................................................................................
** attempts to find the lowest cost path that visits each WhereLoop
** once.  This path is then loaded into the pWInfo->a[].pWLoop fields.
**
** Return SQLITE_OK on success or SQLITE_NOMEM of a memory allocation
** error occurs.
*/
static int wherePathSolver(WhereInfo *pWInfo, double nRowEst){
  int mxChoice = 10;        /* Maximum number of simultaneous paths tracked */
  int nLoop;                /* Number of terms in the join */
  sqlite3 *db;              /* The database connection */
  int iLoop;                /* Loop counter over the terms of the join */
  int ii, jj;               /* Loop counters */
  double rCost;             /* Cost of a path */
  double mxCost;            /* Maximum cost of a set of paths */
  double rSortCost;         /* Cost to do a sort */
................................................................................
  WherePath *pTo;           /* An element of aTo[] that we are working on */
  WhereLoop *pWLoop;        /* One of the WhereLoop objects */
  WhereLoop **pX;           /* Used to divy up the pSpace memory */
  char *pSpace;             /* Temporary memory used by this routine */

  db = pWInfo->pParse->db;
  nLoop = pWInfo->nLevel;
  mxChoice = (nLoop==1) ? 1 : (nLoop==2 ? 5 : 10);
  assert( nLoop<=pWInfo->pTabList->nSrc );
#ifdef WHERETRACE_ENABLED
  if( sqlite3WhereTrace>=2 ) sqlite3DebugPrintf("---- begin solver\n");
#endif

  /* Allocate and initialize space for aTo and aFrom */
  ii = (sizeof(WherePath)+sizeof(WhereLoop*)*nLoop)*mxChoice*2;
  pSpace = sqlite3DbMallocRaw(db, ii);
  if( pSpace==0 ) return SQLITE_NOMEM;
  aTo = (WherePath*)pSpace;
  aFrom = aTo+mxChoice;
  memset(aFrom, 0, sizeof(aFrom[0]));
  pX = (WhereLoop**)(aFrom+mxChoice);
  for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){
    pFrom->aLoop = pX;
  }

  /* Seed the search with a single WherePath containing zero WhereLoops */
  aFrom[0].nRow = (double)1;
  nFrom = 1;