Elefterios Stamatogiannakis wrote:
> On 13/09/12 23:02, Clemens Ladisch wrote:
>> Eleytherios Stamatogiannakis wrote:
>>> Is there a reason for SQLite to not use a covering index for scans?
>>
>> The query optimizer does not allow indexes that are not needed for some
>> DISTINCT, WHERE, or ORDER BY clause:
>
> Do you know if there is a reason for this?
Maybe because it's a special case that nobody has yet bothered to
implement ...
> Thank you for the patch!! With a three line change you replicated the
> new index-only scan feature of PostgreSQL 9.2!
>
> Is there a chance that the change will go into SQLite mainline?
Not without a copyright release.
I dedicate any and all copyright interest in this code and any future
SQLite contributions to the public domain. I make this dedication for
the benefit of the public at large and to the detriment of my heirs
and successors. I intend this dedication to be an overt act of
relinquishment in perpetuity of all present and future rights to this
code under copyright law.
And now v2 without tabstop damage and with much clarified logic:
--- src/where.c
+++ src/where.c
@@ -3037,6 +3037,7 @@
int bSort = !!pOrderBy; /* True if external sort required */
int bDist = !!pDistinct; /* True if index cannot help with DISTINCT */
int bLookup = 0; /* True if not a covering index */
+ int bIndexOnlyScan = 0; /* True for full scan over covering index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
#ifdef SQLITE_ENABLE_STAT3
WhereTerm *pFirstTerm = 0; /* First term matching the index */
@@ -3133,7 +3134,7 @@
** using the main table (i.e. if the index is a covering
** index for this query). If it is, set the WHERE_IDX_ONLY flag in
** wsFlags. Otherwise, set the bLookup variable to true. */
- if( pIdx && wsFlags ){
+ if( pIdx ){
Bitmask m = pSrc->colUsed;
int j;
for(j=0; j<pIdx->nColumn; j++){
@@ -3142,10 +3143,17 @@
m &= ~(((Bitmask)1)<<x);
}
}
+ if( wsFlags ){
- if( m==0 ){
- wsFlags |= WHERE_IDX_ONLY;
- }else{
- bLookup = 1;
+ if( m==0 ){
+ wsFlags |= WHERE_IDX_ONLY;
+ }else{
+ bLookup = 1;
+ }
+ }else if( m==0 ){
+ /* If the index is not otherwise helpful but a covering index, we
+ ** can use it for an index-only scan instead of a full table scan. */
+ wsFlags = WHERE_COLUMN_RANGE|WHERE_IDX_ONLY;
+ bIndexOnlyScan = 1;
}
}
@@ -3209,6 +3217,8 @@
** it seems to be working well enough at the moment.
*/
cost = aiRowEst[0]*4;
+ }else if( bIndexOnlyScan ){
+ cost = aiRowEst[0]*2;
}else{
log10N = estLog(aiRowEst[0]);
cost = nRow;
> For my main workload (OLAP) this can make an enormous difference!
OLAP isn't quite the typical SQLite use case. But do you have any
numbers (which would help deciding whether to accept this patch)?
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
