SQLite

Check-in [05e6e16cb2]
Login

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

Overview
Comment:Add text to the header comment of whereLoopAddBtree() describing how the costs of various b-tree loops are estimated.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | experimental-costs
Files: files | file ages | folders
SHA1: 05e6e16cb28c9ffb4596bd2ef81f687c5403ecbb
User & Date: dan 2014-04-30 15:00:16.197
Context
2014-04-30
15:22
Modify the way the costs of various query plans are estimated. If the user supplies a likelihood() value (or equivalent) on an indexed WHERE constraint, use it to estimate the number of index rows visited. (check-in: 90e3667647 user: dan tags: trunk)
15:00
Add text to the header comment of whereLoopAddBtree() describing how the costs of various b-tree loops are estimated. (Closed-Leaf check-in: 05e6e16cb2 user: dan tags: experimental-costs)
14:53
Update a couple of test cases to account for the fact that this branch prefers an index scan and partial sort over a full-table scan and full external sort. (check-in: 9b975bf33c user: dan tags: experimental-costs)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
4345
4346
4347
4348
4349
4350
4351























4352
4353
4354
4355
4356
4357
4358
  return 0;
}

/*
** Add all WhereLoop objects for a single table of the join where the table
** is idenfied by pBuilder->pNew->iTab.  That table is guaranteed to be
** a b-tree table, not a virtual table.























*/
static int whereLoopAddBtree(
  WhereLoopBuilder *pBuilder, /* WHERE clause information */
  Bitmask mExtra              /* Extra prerequesites for using this table */
){
  WhereInfo *pWInfo;          /* WHERE analysis context */
  Index *pProbe;              /* An index we are evaluating */







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







4345
4346
4347
4348
4349
4350
4351
4352
4353
4354
4355
4356
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
4371
4372
4373
4374
4375
4376
4377
4378
4379
4380
4381
  return 0;
}

/*
** Add all WhereLoop objects for a single table of the join where the table
** is idenfied by pBuilder->pNew->iTab.  That table is guaranteed to be
** a b-tree table, not a virtual table.
**
** The costs (WhereLoop.rRun) of the b-tree loops added by this function
** are calculated as follows:
**
** For a full scan, assuming the table (or index) contains nRow rows:
**
**     cost = nRow * 3.0                    // full-table scan
**     cost = nRow * K                      // scan of covering index
**     cost = nRow * (K+3.0)                // scan of non-covering index
**
** where K is a value between 1.1 and 3.0 set based on the relative 
** estimated average size of the index and table records.
**
** For an index scan, where nVisit is the number of index rows visited
** by the scan, and nSeek is the number of seek operations required on 
** the index b-tree:
**
**     cost = nSeek * (log(nRow) + K * nVisit)          // covering index
**     cost = nSeek * (log(nRow) + (K+3.0) * nVisit)    // non-covering index
**
** Normally, nSeek is 1. nSeek values greater than 1 come about if the 
** WHERE clause includes "x IN (....)" terms used in place of "x=?". Or when 
** implicit "x IN (SELECT x FROM tbl)" terms are added for skip-scans.
*/
static int whereLoopAddBtree(
  WhereLoopBuilder *pBuilder, /* WHERE clause information */
  Bitmask mExtra              /* Extra prerequesites for using this table */
){
  WhereInfo *pWInfo;          /* WHERE analysis context */
  Index *pProbe;              /* An index we are evaluating */