Confirmed
3.6.23.1
sqlite> select count(*) from object_formats join page_table on
page_table.version=3 and page_table.objectid = object_formats.objectid ;
12366
CPU Time: user 0.019997 sys 0.003000
3.7.0
CPU Time: user 32.269095 sys 0.019997
3.7.2
CPU Time: user 33.606891 sys 0.001000
Fossil repository as of 9/25/2010
CPU Time: user 31.087274 sys 0.001000
So...apparently still a bug....
The plan is different:
Slow version:
0|Trace|0|0|0||00|
1|Null|0|1|0||00|
2|Integer|3|2|0||00|
3|Goto|0|22|0||00|
4|OpenRead|0|5|0|2|00|
5|OpenRead|2|9|0|keyinfo(3,BINARY,BINARY)|00|
6|Rewind|0|16|0||00|
7|SCopy|2|3|0||00|
8|Column|0|1|4||00|
9|IsNull|4|15|0||00|
10|Affinity|4|1|0|d|00|
11|SeekGe|2|15|3|2|00|
12|IdxGE|2|15|3|2|01|
13|AggStep|0|0|1|count(0)|00|
14|Next|2|12|0||00|
15|Next|0|7|0||01|
16|Close|0|0|0||00|
17|Close|2|0|0||00|
18|AggFinal|1|0|0|count(0)|00|
19|SCopy|1|6|0||00|
20|ResultRow|6|1|0||00|
21|Halt|0|0|0||00|
22|Transaction|0|0|0||00|
23|VerifyCookie|0|15|0||00|
24|TableLock|0|5|0|object_formats|00|
25|TableLock|0|2|0|page_table|00|
26|Goto|0|4|0||00|
Fast version:
0|Trace|0|0|0||00|
1|Null|0|1|0||00|
2|Integer|3|2|0||00|
3|Goto|0|21|0||00|
4|OpenRead|2|9|0|keyinfo(3,BINARY,BINARY)|00|
5|OpenRead|0|5|0|2|00|
6|SeekGe|2|15|2|1|00|
7|IdxGE|2|15|2|1|01|
8|Rewind|0|14|0||00|
9|Column|2|1|3||00|
10|Column|0|1|4||00|
11|Ne|4|13|3|collseq(BINARY)|6b|
12|AggStep|0|0|1|count(0)|00|
13|Next|0|9|0||01|
14|Next|2|7|0||00|
15|Close|2|0|0||00|
16|Close|0|0|0||00|
17|AggFinal|1|0|0|count(0)|00|
18|SCopy|1|5|0||00|
19|ResultRow|5|1|0||00|
20|Halt|0|0|0||00|
21|Transaction|0|0|0||00|
22|VerifyCookie|0|15|0||00|
23|TableLock|0|2|0|page_table|00|
24|TableLock|0|5|0|object_formats|00|
25|Goto|0|4|0||00|
Bug was introduced on "fossil update 2010-04-16"
633c633
< #define SQLITE_SOURCE_ID "2010-04-14 19:01:45
b87cb0c2bd9c52a938795a974e101879b81210e3"
---
> #define SQLITE_SOURCE_ID "2010-04-15 23:24:29
> f96782b389b5b97b488dc5814f7082e0393f64cd"
83092,83093c83092,83093
< ** subquery does not have both an ORDER BY and a LIMIT clause.
< ** (See ticket #2339)
---
> ** subquery does not have a LIMIT clause.
> ** (See ticket #2339 and ticket [02a8e81d44]).
83176c83176
< if( p->pRightmost && pSub->pLimit && pSub->pOrderBy ){
---
> if( p->pRightmost && pSub->pLimit ){
89670a89671,89675
> /* No OR-clause optimization allowed if the NOT INDEXED clause is used */
> if( pSrc->notIndexed ){
> return;
> }
>
89712a89718,89719
> WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n",
> rTotal, rTotal+nRow*estLog(nRow)));
89714d89720
< WHERETRACE(("... sorting increases OR cost to %.9g\n", rTotal));
90665c90671
< ** nBound:
---
> ** estBound:
90672c90678
< ** nBound to 33. Two constraints (x>? AND x<?) reduce nBound to 11.
---
> ** estBound to 33. Two constraints (x>? AND x<?) reduce estBound to
> 11.
90694c90700,90701
< int nBound = 100;
---
> int estBound = 100;
> int nBound = 0; /* Number of range constraints seen */
90720c90727
< /* Determine the value of nBound. */
---
> /* Determine the value of estBound. */
90726c90733
< whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound);
---
> whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &estBound);
90727a90735
> nBound = 1;
90731a90740
> nBound++;
90800,90801c90809,90810
< nRow = (nRow * (double)nBound) / (double)100;
< cost = (cost * (double)nBound) / (double)100;
---
> nRow = (nRow * (double)estBound) / (double)100;
> cost = (cost * (double)estBound) / (double)100;
90823,90827c90832,90849
< */
< if( nRow>2 && cost<=pCost->rCost ){
< int k;
< int nSkip = nEq;
< Bitmask thisTab = getMask(pWC->pMaskSet, iCur);
---
> ** Also, never reduce the output row count below 2 using this step.
> **
> ** Do not reduce the output row count if pSrc is the only table that
> ** is notReady; if notReady is a power of two. This will be the case
> ** when the main sqlite3WhereBegin() loop is scanning for a table with
> ** and "optimal" index, and on such a scan the output row count
> ** reduction is not valid because it does not update the "pCost->used"
> ** bitmap. The notReady bitmap will also be a power of two when we
> ** are scanning for the last table in a 64-way join. We are willing
> ** to bypass this optimization in that corner case.
> */
> if( nRow>2 && cost<=pCost->rCost && (notReady & (notReady-1))!=0 ){
> int k; /* Loop counter */
> int nSkipEq = nEq; /* Number of == constraints to skip */
> int nSkipRange = nBound; /* Number of <
>
> thisTab = getMask(pWC->pMaskSet, iCur);
90832c90854
< if( nSkip ){
---
> if( nSkipEq ){
90835c90857
< nSkip--;
---
> nSkipEq--;
90840a90863,90872
> }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){
> if( nSkipRange ){
> /* Ignore the first nBound range constraints since the index
> ** has already accounted for these */
> nSkipRange--;
> }else{
> /* Assume each additional range constraint reduces the result
> ** set size by a factor of 3 */
> nRow /= 3;
> }
90851c90883
< "%s(%s): nEq=%d nInMul=%d nBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
---
> "%s(%s): nEq=%d nInMul=%d estBound=%d bSort=%d bLookup=%d
> wsFlags=0x%x\n"
90854c90886,90887
< nEq, nInMul, nBound, bSort, bLookup, wsFlags, notReady, nRow, cost, used
---
> nEq, nInMul, estBound, bSort, bLookup, wsFlags,
> notReady, nRow, cost, used
90861c90894
< && (cost<pCost->rCost || (cost==pCost->rCost && nRow<pCost->nRow))
---
> && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->nRow))
92111c92144
< ** next nested loop. The FROM clause entries may be iterated through
---
> ** next nested loop. The loop tests all FROM clause entries
92114,92115c92147,92149
< ** The first iteration, which is always performed, searches for the
< ** FROM clause entry that permits the lowest-cost, "optimal" scan. In
---
> ** The first test is always performed if there are two or more entries
> ** remaining and never performed if there is only one FROM clause entry
> ** to choose from. The first test looks for an "optimal" scan. In
92120c92154,92158
< ** by waiting for other tables to run first.
---
> ** by waiting for other tables to run first. This "optimal" test works
> ** by first assuming that the FROM clause is on the inner loop and finding
> ** its query plan, then checking to see if that query plan uses any
> ** other FROM clause terms that are notReady. If no notReady terms are
> ** used then the "optimal" query plan works.
92122,92124c92160,92162
< ** The second iteration is only performed if no optimal scan strategies
< ** were found by the first. This iteration is used to search for the
< ** lowest cost scan overall.
---
> ** The second loop iteration is only performed if no optimal scan
> ** strategies were found by the first loop. This 2nd iteration is used to
> ** search for the lowest cost scan overall.
92142,92144c92180,92181
< for(isOptimal=1; isOptimal>=0 && bestJ<0; isOptimal--){
< Bitmask mask = (isOptimal ? 0 : notReady);
< assert( (nTabList-iFrom)>1 || isOptimal );
---
> for(isOptimal=(iFrom<nTabList-1); isOptimal>=0; isOptimal--){
> Bitmask mask; /* Mask of tables not yet ready */
92156a92194
> mask = (isOptimal ? m : notReady);
92172,92173c92210,92211
< && (j==iFrom || sCost.rCost<bestPlan.rCost
< || (sCost.rCost==bestPlan.rCost && sCost.nRow<bestPlan.nRow))
---
> && (bestJ<0 || sCost.rCost<bestPlan.rCost
> || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
92174a92213,92214
> WHERETRACE(("... best so far with cost=%g and nRow=%g\n",
> sCost.rCost, sCost.nRow));
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: [email protected] on behalf of Charles Samuels
Sent: Fri 9/24/2010 7:03 PM
To: [email protected]
Subject: EXTERNAL:[sqlite] Massive performance regression in 3.7.x
Greetings,
With the sqlite database here:
http://www.derkarl.org/~charles/massive_performance_regression.bz2
There is a massive performance regression between 3.7.1 and 3.7.2, when
compared to 3.6.23.1.
The following query runs orders of magnitude slower than it did in the
previous version:
select *
from object_formats join
page_table on page_table.version=3 and page_table.objectid =
object_formats.objectid ;
Thanks,
Charles
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
