/ Check-in [301bbee4]
Login

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

Overview
Comment:Bug fixes in the ORDER BY optimizer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: 301bbee4045aa169e29fb4fb75743b71eb4760a1
User & Date: drh 2012-10-08 19:41:38
Context
2012-10-08
20:27
Further tweaks to the ORDER BY optimizer, to fix a bug and to get the optimizer to recognize some additional cases. check-in: bcb4f262 user: drh tags: qp-enhancements
19:41
Bug fixes in the ORDER BY optimizer. check-in: 301bbee4 user: drh tags: qp-enhancements
18:23
Continued refactoring of the ORDER BY optimization logic. This check-in is close to working, but it still has issues. A few test cases fail. check-in: adbdc663 user: drh tags: qp-enhancements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2799   2799     struct ExprList_item *pOBItem;/* A term of the ORDER BY clause */
  2800   2800     Table *pTab = pIdx->pTable;   /* Table that owns index pIdx */
  2801   2801     ExprList *pOrderBy;           /* The ORDER BY clause */
  2802   2802     Parse *pParse = p->pParse;    /* Parser context */
  2803   2803     sqlite3 *db = pParse->db;     /* Database connection */
  2804   2804     int nPriorSat;                /* ORDER BY terms satisfied by outer loops */
  2805   2805     int seenRowid = 0;            /* True if an ORDER BY rowid term is seen */
  2806         -  int uniqueNotNull = 1;        /* pIdx is UNIQUE with all terms are NOT NULL */
         2806  +  int uniqueNotNull;            /* pIdx is UNIQUE with all terms are NOT NULL */
  2807   2807   
  2808   2808     if( p->i==0 ){
  2809   2809       nPriorSat = 0;
  2810   2810     }else{
  2811   2811       nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
  2812         -    if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return nPriorSat;
         2812  +    if( (p->aLevel[p->i-1].plan.wsFlags & WHERE_ORDERED)==0 ){
         2813  +      /* This loop cannot be ordered unless the next outer loop is
         2814  +      ** also ordered */
         2815  +      return nPriorSat;
         2816  +    }
         2817  +    if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ){
         2818  +      /* Only look at the outer-most loop if the OrderByIdxJoin
         2819  +      ** optimization is disabled */
         2820  +      return nPriorSat;
         2821  +    }
  2813   2822     }
  2814   2823     pOrderBy = p->pOrderBy;
  2815   2824     assert( pOrderBy!=0 );
  2816         -  if( pIdx->bUnordered ) return nPriorSat;
         2825  +  if( pIdx->bUnordered ){
         2826  +    /* Hash indices (indicated by the "unordered" tag on sqlite_stat1) cannot
         2827  +    ** be used for sorting */
         2828  +    return nPriorSat;
         2829  +  }
  2817   2830     nTerm = pOrderBy->nExpr;
  2818   2831     uniqueNotNull = pIdx->onError!=OE_None;
  2819   2832     assert( nTerm>0 );
  2820   2833   
  2821   2834     /* Argument pIdx must either point to a 'real' named index structure, 
  2822   2835     ** or an index structure allocated on the stack by bestBtreeIndex() to
  2823   2836     ** represent the rowid index that is part of every table.  */
................................................................................
  2932   2945       if( iColumn<0 ){
  2933   2946         seenRowid = 1;
  2934   2947         break;
  2935   2948       }else if( pTab->aCol[iColumn].notNull==0 ){
  2936   2949         uniqueNotNull = 0;
  2937   2950       }
  2938   2951     }
         2952  +
         2953  +  /* If we have not found at least one ORDER BY term that matches the
         2954  +  ** index, then show no progress. */
         2955  +  if( pOBItem==&pOrderBy->a[nPriorSat] ) return nPriorSat;
         2956  +
         2957  +  /* Return the necessary scan order back to the caller */
  2939   2958     *pbRev = sortOrder & 1;
  2940   2959   
  2941   2960     /* If there was an "ORDER BY rowid" term that matched, or it is only
  2942   2961     ** possible for a single row from this table to match, then skip over
  2943   2962     ** any additional ORDER BY terms dealing with this table.
  2944   2963     */
  2945   2964     if( seenRowid || (uniqueNotNull && i>=pIdx->nColumn) ){