Documentation Source Text
Check-in [232b584bda]
Not logged in

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

Overview
SHA1 Hash:232b584bda3722bac4175ae7cfdc3a7f2d94bacc
Date: 2013-07-17 00:17:20
User: drh
Comment:Tweaks to the text describing the hazards of upgrading to the NGQP.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/queryplanner-ng.in

356
357
358
359
360
361
362
363
364
365


366




















367
368
369
370
371
372
373
and recompile and the application will run faster.  
There are no API changes nor modifications
to compilation procedures.</p>

<p>But as with any query planner change, upgrading to the NGQP does carry
a small risk of introducing performance regressions.  The problem here is
not that the NGQP is incorrect or buggy or inferior to the legacy query
planner.  Given accurate cost estimates, the NGQP will always pick a better
plan than older query planners.  The problem is that cost estimates might
sometimes be inaccurate and the legacy query planner just happened to stumbled 


over a good plan by stupid luck while the NGQP is not as lucky.</p>





















<tcl>hd_fragment fossilcasestudy {The Fossil NGQP Upgrade Case Study}</tcl>
<h3>4.1 Case Study: Upgrading Fossil to the NGQP</h3>

<p>The <a href="http://www.fossil-scm.org/">Fossil DVCS</a> is the version
control system used to track all of the SQLite source code.
A Fossil repository is an SQLite database file.







|
|
|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
and recompile and the application will run faster.  
There are no API changes nor modifications
to compilation procedures.</p>

<p>But as with any query planner change, upgrading to the NGQP does carry
a small risk of introducing performance regressions.  The problem here is
not that the NGQP is incorrect or buggy or inferior to the legacy query
planner.  Given reliable information about the selectivity of indices, 
the NGQP should always pick a plan than is as good or better than before.
The problem is that some applications may be using low-quality and
low-selectivity indices without having run [ANALYZE].  The older query
planners look at many fewer possible implementations for each query and 
so they may have stumbled over a good plan by stupid luck.  The NGQP, on 
the other hand, looks at many more query plan possibilities, and it may 
chose a different query plan that
works better in theory, assuming good indices, but which gives a performance
regression in practice, because of the shape of the data.</p>

<p>Key points:</p>

<ul>
<li><p>The NGQP will always find an equal or better query plan, compared to
    prior query planners, as long as it
    has access to accurate [ANALYZE] data in the [SQLITE_STAT1] file.</p>
<li><p>The NGQP will always find a good query plan 
    as long as the schema does not contain indices that have more than
    about 10 or 20 rows with the same value in the left-most column of the
    index.</p>
</ul>

<p>Not all applications meet these conditions.  Fortunately,
the NGQP will still usually find good query plans, even without these conditions.
However, cases do arise (rarely) where performance regressions can occur.</p>

<tcl>hd_fragment fossilcasestudy {The Fossil NGQP Upgrade Case Study}</tcl>
<h3>4.1 Case Study: Upgrading Fossil to the NGQP</h3>

<p>The <a href="http://www.fossil-scm.org/">Fossil DVCS</a> is the version
control system used to track all of the SQLite source code.
A Fossil repository is an SQLite database file.