Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In ORDER BY LIMIT queries, try to evaluate the ORDER BY terms first, and it it becomes clear that the row will not come in under the LIMIT, then skip evaluation of the other columns. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
c381f0ea57002a264fd958b28e4921cb |
User & Date: | drh 2018-05-03 23:20:06 |
References
2018-05-05
| ||
16:50 | In an ORDER BY LIMIT, make sure the ORDER BY expression evaluator does not try to reuse values from the result set if the result set has not yet be computed. This fixes a bug in the recent deferred-row loading optimization, check-in [c381f0ea57002a264fd958b28e]. OSSFuzz discovered the problem. (check-in: 5d61e75f user: drh tags: trunk) | |
Context
2018-05-04
| ||
00:39 | Make a separate limb in the EXPLAIN QUERY PLAN output for the various lines associated with the OR-optimization. (check-in: 75ac7b4e user: drh tags: trunk) | |
2018-05-03
| ||
23:20 | In ORDER BY LIMIT queries, try to evaluate the ORDER BY terms first, and it it becomes clear that the row will not come in under the LIMIT, then skip evaluation of the other columns. (check-in: c381f0ea user: drh tags: trunk) | |
22:52 | Fix a branch that has become unreachable due to recent enhancements. (check-in: 81ab5e0d user: drh tags: trunk) | |
2018-04-30
| ||
19:32 | Defer loading result column values into registers on an ORDER BY LIMIT until we know that the LIMIT does not exclude the current row. (Closed-Leaf check-in: ce4ef460 user: drh tags: faster-order-by-limit) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
74 75 76 77 78 79 80 81 82 83 84 85 86 87 | u8 nDefer; /* Number of valid entries in aDefer[] */ struct DeferredCsr { Table *pTab; /* Table definition */ int iCsr; /* Cursor number for table */ int nKey; /* Number of PK columns for table pTab (>=1) */ } aDefer[4]; #endif }; #define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */ /* ** Delete all the content of a Select structure. Deallocate the structure ** itself only if bFree is true. */ | > | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | u8 nDefer; /* Number of valid entries in aDefer[] */ struct DeferredCsr { Table *pTab; /* Table definition */ int iCsr; /* Cursor number for table */ int nKey; /* Number of PK columns for table pTab (>=1) */ } aDefer[4]; #endif struct RowLoadInfo *pDeferredRowLoad; /* Deferred row loading info or NULL */ }; #define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */ /* ** Delete all the content of a Select structure. Deallocate the structure ** itself only if bFree is true. */ |
︙ | ︙ | |||
531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 | /* Forward reference */ static KeyInfo *keyInfoFromExprList( Parse *pParse, /* Parsing context */ ExprList *pList, /* Form the KeyInfo object from this ExprList */ int iStart, /* Begin with this column of pList */ int nExtra /* Add this many extra columns to the end */ ); /* ** Generate code that will push the record in registers regData ** through regData+nData-1 onto the sorter. */ static void pushOntoSorter( Parse *pParse, /* Parser context */ SortCtx *pSort, /* Information about the ORDER BY clause */ Select *pSelect, /* The whole SELECT statement */ int regData, /* First register holding data to be sorted */ int regOrigData, /* First register holding data before packing */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > > > > > > > > > > | | 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 | /* Forward reference */ static KeyInfo *keyInfoFromExprList( Parse *pParse, /* Parsing context */ ExprList *pList, /* Form the KeyInfo object from this ExprList */ int iStart, /* Begin with this column of pList */ int nExtra /* Add this many extra columns to the end */ ); /* ** An instance of this object holds information (beyond pParse and pSelect) ** needed to load the next result row that is to be added to the sorter. */ typedef struct RowLoadInfo RowLoadInfo; struct RowLoadInfo { int regResult; /* Store results in array of registers here */ u8 ecelFlags; /* Flag argument to ExprCodeExprList() */ #ifdef SQLITE_ENABLE_SORTER_REFERENCES ExprList *pExtra; /* Extra columns needed by sorter refs */ int regExtraResult; /* Where to load the extra columns */ #endif }; /* ** This routine does the work of loading query data into an array of ** registers so that it can be added to the sorter. */ static void innerLoopLoadRow( Parse *pParse, /* Statement under construction */ Select *pSelect, /* The query being coded */ RowLoadInfo *pInfo /* Info needed to complete the row load */ ){ sqlite3ExprCodeExprList(pParse, pSelect->pEList, pInfo->regResult, 0, pInfo->ecelFlags); #ifdef SQLITE_ENABLE_SORTER_REFERENCES if( pInfo->pExtra ){ sqlite3ExprCodeExprList(pParse, pInfo->pExtra, pInfo->regExtraResult, 0, 0); sqlite3ExprListDelete(pParse->db, pInfo->pExtra); } #endif } /* ** Code the OP_MakeRecord instruction that generates the entry to be ** added into the sorter. ** ** Return the register in which the result is stored. */ static int makeSorterRecord( Parse *pParse, SortCtx *pSort, Select *pSelect, int regBase, int nBase ){ int nOBSat = pSort->nOBSat; Vdbe *v = pParse->pVdbe; int regOut = ++pParse->nMem; if( pSort->pDeferredRowLoad ){ innerLoopLoadRow(pParse, pSelect, pSort->pDeferredRowLoad); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regOut); return regOut; } /* ** Generate code that will push the record in registers regData ** through regData+nData-1 onto the sorter. */ static void pushOntoSorter( Parse *pParse, /* Parser context */ SortCtx *pSort, /* Information about the ORDER BY clause */ Select *pSelect, /* The whole SELECT statement */ int regData, /* First register holding data to be sorted */ int regOrigData, /* First register holding data before packing */ int nData, /* Number of elements in the regData data array */ int nPrefixReg /* No. of reg prior to regData available for use */ ){ Vdbe *v = pParse->pVdbe; /* Stmt under construction */ int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0); int nExpr = pSort->pOrderBy->nExpr; /* No. of ORDER BY terms */ int nBase = nExpr + bSeq + nData; /* Fields in sorter record */ int regBase; /* Regs for sorter record */ int regRecord = 0; /* Assembled sorter record */ int nOBSat = pSort->nOBSat; /* ORDER BY terms to skip */ int op; /* Opcode to add sorter record to sorter */ int iLimit; /* LIMIT counter */ int iSkip = 0; /* End of the sorter insert loop */ assert( bSeq==0 || bSeq==1 ); /* Three cases: ** (1) The data to be sorted has already been packed into a Record ** by a prior OP_MakeRecord. In this case nData==1 and regData ** will be completely unrelated to regOrigData. ** (2) All output columns are included in the sort record. In that ** case regData==regOrigData. ** (3) Some output columns are omitted from the sort record due to ** the SQLITE_ENABLE_SORTER_REFERENCE optimization, or due to the ** SQLITE_ECEL_OMITREF optimization. In that case, regOrigData==0 ** to prevent this routine from trying to copy values that might ** not exist. */ assert( nData==1 || regData==regOrigData || regOrigData==0 ); if( nPrefixReg ){ assert( nPrefixReg==nExpr+bSeq ); regBase = regData - nPrefixReg; }else{ regBase = pParse->nMem + 1; pParse->nMem += nBase; } assert( pSelect->iOffset==0 || pSelect->iLimit!=0 ); iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit; pSort->labelDone = sqlite3VdbeMakeLabel(v); |
︙ | ︙ | |||
583 584 585 586 587 588 589 | int regPrevKey; /* The first nOBSat columns of the previous row */ int addrFirst; /* Address of the OP_IfNot opcode */ int addrJmp; /* Address of the OP_Jump opcode */ VdbeOp *pOp; /* Opcode that opens the sorter */ int nKey; /* Number of sorting key columns, including OP_Sequence */ KeyInfo *pKI; /* Original KeyInfo on the sorter table */ | | | 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | int regPrevKey; /* The first nOBSat columns of the previous row */ int addrFirst; /* Address of the OP_IfNot opcode */ int addrJmp; /* Address of the OP_Jump opcode */ VdbeOp *pOp; /* Opcode that opens the sorter */ int nKey; /* Number of sorting key columns, including OP_Sequence */ KeyInfo *pKI; /* Original KeyInfo on the sorter table */ regRecord = makeSorterRecord(pParse, pSort, pSelect, regBase, nBase); regPrevKey = pParse->nMem+1; pParse->nMem += pSort->nOBSat; nKey = nExpr - pSort->nOBSat + bSeq; if( bSeq ){ addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); }else{ addrFirst = sqlite3VdbeAddOp1(v, OP_SequenceTest, pSort->iECursor); |
︙ | ︙ | |||
634 635 636 637 638 639 640 | ** If the new record does not need to be inserted into the sorter, ** jump to the next iteration of the loop. Or, if the ** pSort->bOrderedInnerLoop flag is set to indicate that the inner ** loop delivers items in sorted order, jump to the next iteration ** of the outer loop. */ int iCsr = pSort->iECursor; | < < | > | | > > > > > | 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 | ** If the new record does not need to be inserted into the sorter, ** jump to the next iteration of the loop. Or, if the ** pSort->bOrderedInnerLoop flag is set to indicate that the inner ** loop delivers items in sorted order, jump to the next iteration ** of the outer loop. */ int iCsr = pSort->iECursor; sqlite3VdbeAddOp2(v, OP_IfNotZero, iLimit, sqlite3VdbeCurrentAddr(v)+4); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Last, iCsr, 0); iSkip = sqlite3VdbeAddOp4Int(v, OP_IdxLE, iCsr, 0, regBase+nOBSat, nExpr-nOBSat); VdbeCoverage(v); sqlite3VdbeAddOp1(v, OP_Delete, iCsr); } if( regRecord==0 ){ regRecord = makeSorterRecord(pParse, pSort, pSelect, regBase, nBase); } if( pSort->sortFlags & SORTFLAG_UseSorter ){ op = OP_SorterInsert; }else{ op = OP_IdxInsert; } sqlite3VdbeAddOp4Int(v, op, pSort->iECursor, regRecord, regBase+nOBSat, nBase-nOBSat); if( iSkip ){ assert( pSort->bOrderedInnerLoop==0 || pSort->bOrderedInnerLoop==1 ); sqlite3VdbeChangeP2(v, iSkip, sqlite3VdbeCurrentAddr(v) + pSort->bOrderedInnerLoop); } } /* ** Add code to implement the OFFSET */ static void codeOffset( Vdbe *v, /* Generate code into this VM */ |
︙ | ︙ | |||
738 739 740 741 742 743 744 | for(i=0; i<pEList->nExpr; i++){ struct ExprList_item *pItem = &pEList->a[i]; if( pItem->u.x.iOrderByCol==0 ){ Expr *pExpr = pItem->pExpr; Table *pTab = pExpr->pTab; if( pExpr->op==TK_COLUMN && pTab && !IsVirtual(pTab) && (pTab->aCol[pExpr->iColumn].colFlags & COLFLAG_SORTERREF) | < < < | 814 815 816 817 818 819 820 821 822 823 824 825 826 827 | for(i=0; i<pEList->nExpr; i++){ struct ExprList_item *pItem = &pEList->a[i]; if( pItem->u.x.iOrderByCol==0 ){ Expr *pExpr = pItem->pExpr; Table *pTab = pExpr->pTab; if( pExpr->op==TK_COLUMN && pTab && !IsVirtual(pTab) && (pTab->aCol[pExpr->iColumn].colFlags & COLFLAG_SORTERREF) ){ int j; for(j=0; j<nDefer; j++){ if( pSort->aDefer[j].iCsr==pExpr->iTable ) break; } if( j==nDefer ){ if( nDefer==ArraySize(pSort->aDefer) ){ |
︙ | ︙ | |||
807 808 809 810 811 812 813 814 815 816 817 818 819 820 | Vdbe *v = pParse->pVdbe; int i; int hasDistinct; /* True if the DISTINCT keyword is present */ int eDest = pDest->eDest; /* How to dispose of results */ int iParm = pDest->iSDParm; /* First argument to disposal method */ int nResultCol; /* Number of result columns */ int nPrefixReg = 0; /* Number of extra registers before regResult */ /* Usually, regResult is the first cell in an array of memory cells ** containing the current result row. In this case regOrig is set to the ** same value. However, if the results are being sent to the sorter, the ** values for any expressions that are also part of the sort-key are omitted ** from this array. In this case regOrig is set to zero. */ int regResult; /* Start of memory holding current results */ | > | 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 | Vdbe *v = pParse->pVdbe; int i; int hasDistinct; /* True if the DISTINCT keyword is present */ int eDest = pDest->eDest; /* How to dispose of results */ int iParm = pDest->iSDParm; /* First argument to disposal method */ int nResultCol; /* Number of result columns */ int nPrefixReg = 0; /* Number of extra registers before regResult */ RowLoadInfo sRowLoadInfo; /* Info for deferred row loading */ /* Usually, regResult is the first cell in an array of memory cells ** containing the current result row. In this case regOrig is set to the ** same value. However, if the results are being sent to the sorter, the ** values for any expressions that are also part of the sort-key are omitted ** from this array. In this case regOrig is set to zero. */ int regResult; /* Start of memory holding current results */ |
︙ | ︙ | |||
859 860 861 862 863 864 865 | }else if( eDest!=SRT_Exists ){ #ifdef SQLITE_ENABLE_SORTER_REFERENCES ExprList *pExtra = 0; #endif /* If the destination is an EXISTS(...) expression, the actual ** values returned by the SELECT are not required. */ | | > > > > > > > > > > > > > | > > > > > > > > | | | | | > > > > | > > > > | < | 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 | }else if( eDest!=SRT_Exists ){ #ifdef SQLITE_ENABLE_SORTER_REFERENCES ExprList *pExtra = 0; #endif /* If the destination is an EXISTS(...) expression, the actual ** values returned by the SELECT are not required. */ u8 ecelFlags; /* "ecel" is an abbreviation of "ExprCodeExprList" */ ExprList *pEList; if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){ ecelFlags = SQLITE_ECEL_DUP; }else{ ecelFlags = 0; } if( pSort && hasDistinct==0 && eDest!=SRT_EphemTab && eDest!=SRT_Table ){ /* For each expression in p->pEList that is a copy of an expression in ** the ORDER BY clause (pSort->pOrderBy), set the associated ** iOrderByCol value to one more than the index of the ORDER BY ** expression within the sort-key that pushOntoSorter() will generate. ** This allows the p->pEList field to be omitted from the sorted record, ** saving space and CPU cycles. */ ecelFlags |= (SQLITE_ECEL_OMITREF|SQLITE_ECEL_REF); for(i=pSort->nOBSat; i<pSort->pOrderBy->nExpr; i++){ int j; if( (j = pSort->pOrderBy->a[i].u.x.iOrderByCol)>0 ){ p->pEList->a[j-1].u.x.iOrderByCol = i+1-pSort->nOBSat; } } #ifdef SQLITE_ENABLE_SORTER_REFERENCES selectExprDefer(pParse, pSort, p->pEList, &pExtra); if( pExtra && pParse->db->mallocFailed==0 ){ /* If there are any extra PK columns to add to the sorter records, ** allocate extra memory cells and adjust the OpenEphemeral ** instruction to account for the larger records. This is only ** required if there are one or more WITHOUT ROWID tables with ** composite primary keys in the SortCtx.aDefer[] array. */ VdbeOp *pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex); pOp->p2 += (pExtra->nExpr - pSort->nDefer); pOp->p4.pKeyInfo->nAllField += (pExtra->nExpr - pSort->nDefer); pParse->nMem += pExtra->nExpr; } #endif /* Adjust nResultCol to account for columns that are omitted ** from the sorter by the optimizations in this branch */ pEList = p->pEList; for(i=0; i<pEList->nExpr; i++){ if( pEList->a[i].u.x.iOrderByCol>0 #ifdef SQLITE_ENABLE_SORTER_REFERENCES || pEList->a[i].bSorterRef #endif ){ nResultCol--; regOrig = 0; } } testcase( regOrig ); testcase( eDest==SRT_Set ); testcase( eDest==SRT_Mem ); testcase( eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); assert( eDest==SRT_Set || eDest==SRT_Mem || eDest==SRT_Coroutine || eDest==SRT_Output ); } sRowLoadInfo.regResult = regResult; sRowLoadInfo.ecelFlags = ecelFlags; #ifdef SQLITE_ENABLE_SORTER_REFERENCES sRowLoadInfo.pExtra = pExtra; sRowLoadInfo.regExtraResult = regResult + nResultCol; if( pExtra ) nResultCol += pExtra->nExpr; #endif if( p->iLimit && (ecelFlags & SQLITE_ECEL_OMITREF)!=0 && nPrefixReg>0 ){ assert( pSort!=0 ); assert( hasDistinct==0 ); pSort->pDeferredRowLoad = &sRowLoadInfo; }else{ innerLoopLoadRow(pParse, p, &sRowLoadInfo); } } /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( hasDistinct ){ |
︙ | ︙ | |||
1022 1023 1024 1025 1026 1027 1028 | sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); VdbeCoverage(v); sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm+1, r1,regResult,nResultCol); assert( pSort==0 ); } #endif if( pSort ){ | > | | 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 | sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); VdbeCoverage(v); sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm+1, r1,regResult,nResultCol); assert( pSort==0 ); } #endif if( pSort ){ assert( regResult==regOrig ); pushOntoSorter(pParse, pSort, p, r1+nPrefixReg, regOrig, 1, nPrefixReg); }else{ int r2 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2); sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); sqlite3ReleaseTempReg(pParse, r2); } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2752 2753 2754 2755 2756 2757 2758 | int iIdxCur; /* Index of the first index cursor */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** | | < < | < | 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 | int iIdxCur; /* Index of the first index cursor */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** See the header comment on the computeLimitRegisters() routine for a ** detailed description of the meaning of the iLimit and iOffset fields. ** ** addrOpenEphm[] entries contain the address of OP_OpenEphemeral opcodes. ** These addresses must be stored so that we can go back and fill in ** the P4_KEYINFO and P2 parameters later. Neither the KeyInfo nor ** the number of columns in P2 can be computed at the same time ** as the OP_OpenEphm instruction is coded because not ** enough information about the compound query is known at that point. |
︙ | ︙ |