SQLite

Check-in [0852c57e]
Login

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

Overview
Comment:For the purpose of the query planner heuristic added by [38db9b5c83], a query should only count as a star query if the fact tables are connected to the dimension table by an INNER JOIN. If a LEFT JOIN is used, then the fact tables are constrained to be in inner loops anyhow and so the heuristic does not make any sense. But it does interfere with AUTOMATIC index creation, which causes the performance regression reported by forum post d87570a1455.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | trunk
Files: files | file ages | folders
SHA3-256: 0852c57ee2768224af79910e6f26e70a4962651dae0f8b45cbfc847e6707d7bb
User & Date: drh 2025-01-17 23:49:40
Context
2025-01-17
23:49
For the purpose of the query planner heuristic added by [38db9b5c83], a query should only count as a star query if the fact tables are connected to the dimension table by an INNER JOIN. If a LEFT JOIN is used, then the fact tables are constrained to be in inner loops anyhow and so the heuristic does not make any sense. But it does interfere with AUTOMATIC index creation, which causes the performance regression reported by forum post d87570a1455. (Leaf check-in: 0852c57e user: drh tags: trunk)
17:09
Enhance a comment on a test case to add recent context. (check-in: c2647d1b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
5423
5424
5425
5426
5427
5428
5429
5430

5431
5432
5433
5434
5435
5436
5437
5438
5439

**
** The value returned is a tuning parameter.  Currently the value is:
**
**     18    for star queries
**     12    otherwise
**
** For the purposes of SQLite, a star-query is defined as a query
** with a large central table that is joined against four or more

** smaller tables.  The central table is called the "fact" table.
** The smaller tables that get joined are "dimension tables".
**
** SIDE EFFECT:  (and really the whole point of this subroutine)
**
** If pWInfo describes a star-query, then the cost on WhereLoops for the
** fact table is reduced.  This heuristic helps keep fact tables in
** outer loops.  Without this heuristic, paths with fact tables in outer
** loops tend to get pruned by the mxChoice limit on the number of paths,








|
>
|
|








5423
5424
5425
5426
5427
5428
5429
5430
5431
5432
5433
5434
5435
5436
5437
5438
5439
5440

**
** The value returned is a tuning parameter.  Currently the value is:
**
**     18    for star queries
**     12    otherwise
**
** For the purposes of SQLite, a star-query is defined as a query
** with a large central table that is joined (using an INNER JOIN,
** not a LEFT JOIN) against four or more smaller tables.  The central
** table is called the "fact" table.  The smaller tables that get
** joined are "dimension tables".
**
** SIDE EFFECT:  (and really the whole point of this subroutine)
**
** If pWInfo describes a star-query, then the cost on WhereLoops for the
** fact table is reduced.  This heuristic helps keep fact tables in
** outer loops.  Without this heuristic, paths with fact tables in outer
** loops tend to get pruned by the mxChoice limit on the number of paths,
︙︙
5453
5454
5455
5456
5457
5458
5459

5460



5461
5462
5463
5464
5465
5466
5467

    assert( pWInfo->nOutStarDelta==0 );
    for(iLoop=0, m=1; iLoop<nLoop; iLoop++, m<<=1){
      WhereLoop *pWLoop;        /* For looping over WhereLoops */
      int nDep = 0;             /* Number of dimension tables */
      LogEst rDelta;            /* Heuristic cost adjustment */
      Bitmask mSeen = 0;        /* Mask of dimension tables */
      for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){

        if( (pWLoop->prereq & m)!=0 && (pWLoop->maskSelf & mSeen)==0 ){



          nDep++;
          mSeen |= pWLoop->maskSelf;
        }
      }
      if( nDep<=3 ) continue;
      rDelta = 15*(nDep-3);
#ifdef WHERETRACE_ENABLED /* 0x4 */








>
|
>
>
>








5454
5455
5456
5457
5458
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
5469
5470
5471
5472

    assert( pWInfo->nOutStarDelta==0 );
    for(iLoop=0, m=1; iLoop<nLoop; iLoop++, m<<=1){
      WhereLoop *pWLoop;        /* For looping over WhereLoops */
      int nDep = 0;             /* Number of dimension tables */
      LogEst rDelta;            /* Heuristic cost adjustment */
      Bitmask mSeen = 0;        /* Mask of dimension tables */
      for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){
        if( (pWLoop->prereq & m)!=0        /* pWInfo depends on iLoop */
         && (pWLoop->maskSelf & mSeen)==0  /* pWInfo not already a dependency */
         && (pWInfo->pTabList->a[pWLoop->iTab].fg.jointype & JT_LEFT)==0
                                               /* ^- pWInfo isn't a LEFT JOIN */
        ){
          nDep++;
          mSeen |= pWLoop->maskSelf;
        }
      }
      if( nDep<=3 ) continue;
      rDelta = 15*(nDep-3);
#ifdef WHERETRACE_ENABLED /* 0x4 */
︙︙