Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-38145

N1QL generated from Eventing is not using proper indexes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.5.0, 6.0.3
    • Fix Version/s: 6.5.1
    • Component/s: eventing
    • Labels:
    • Triage:
      Untriaged
    • Flagged:
      Release Note
    • Is this a Regression?:
      No

      Description

      Jon Strabala reports:
       
      I simplified the handler and wrote a 12 page report to highlights the issue (see: TESTV2.zip)I was testing with a build that failed Enterprise Edition 6.5.1 build 6192 (Feb 22 build) - and there are a lot of builds that fail as per my list below:We need to file a MB on this and also a release note for 6.03 GA and also 6.5 GA as it can lead to degenerate performance.
       
      WORKS SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
      Enterprise Edition 6.0.0 build 1693 (Oct 11 2018 build) - I think this is GA 6.0.0
      Enterprise Edition 6.0.1 build 2037 (Jan 21 2019 build) - I think this is GA 6.0.1
      Enterprise Edition 6.0.2 build 2413 (Jun 12 2019 build) - I think this is GA 6.0.2
       
      WRONG BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION
      Enterprise Edition 6.0.3 build 2895 (Oct 09 2019 build) - I think this is GA 6.0.3
       
      WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
      Enterprise Edition 6.0.4 build 3082 (Feb 13 Build) - I think this is GA 6.0.4
       
      WRONG (AGAIN) BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION
      Enterprise Edition 6.5.0 build 4960 (Jan 13 Build) - I think this is GA 6.5
      Enterprise Edition 6.5.1 build 6120 (Feb 04 build)
      Enterprise Edition 6.5.1 build 6154 (Feb 11 build)
      Enterprise Edition 6.5.1 build 6168 (Feb 14 build)
      Enterprise Edition 6.5.1 build 6186 (Feb 20 build)
      Enterprise Edition 6.5.1 build 6191 (Feb 21 build)
      Enterprise Edition 6.5.1 build 6192 (Feb 22 build)
      Enterprise Edition 6.5.1 build 6193 (Feb 22 build)
       
      WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
      Enterprise Edition 6.5.1 build 6194 (Feb 24 build)
      *
      *
      Enterprise Edition 6.5.1 build 6218 (Feb 28 build)
      Enterprise Edition 6.5.1 build 6221 (Mar 02 build)
       
      Note: if you run my Eventing code "basic_index_path_issue.json" on versions 6.0.X (before 6.5) you must set var "ITER_CLOSE = false" as 6.0.X dose note support .close() on an iterable handle
       

        Attachments

          Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

            Activity

            siri Sriram Melkote (Inactive) created issue -
            siri Sriram Melkote (Inactive) made changes -
            Field Original Value New Value
            Affects Version/s 6.5.0 [ 15037 ]
            siri Sriram Melkote (Inactive) made changes -
            Fix Version/s 6.5.1 [ 16622 ]
            siri Sriram Melkote (Inactive) made changes -
            Link This issue backports to MB-38146 [ MB-38146 ]
            Hide
            siri Sriram Melkote (Inactive) added a comment -

            This is likely fixed as a result of MB-37633 but we should release note this bug instead of MB-37633 as this is much more visible result of preparing the statement.

            Show
            siri Sriram Melkote (Inactive) added a comment - This is likely fixed as a result of  MB-37633 but we should release note this bug instead of MB-37633 as this is much more visible result of preparing the statement.
            siri Sriram Melkote (Inactive) made changes -
            Link This issue is duplicated by MB-38146 [ MB-38146 ]
            siri Sriram Melkote (Inactive) made changes -
            Link This issue is duplicated by MB-38146 [ MB-38146 ]
            siri Sriram Melkote (Inactive) made changes -
            Link This issue duplicates MB-37633 [ MB-37633 ]
            siri Sriram Melkote (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Resolved [ 5 ]
            jon.strabala Jon Strabala made changes -
            Description [~jon.strabala] reports:
             
            I simplified the handler and wrote a 12 page report to highlights the issue (see: TESTV2.zip)I was testing with a build that failed Enterprise Edition 6.5.1 build 6192 (Feb 22 build) - and there are a lot of builds that fail as per my list below:*We need to file a MB on this and also a release note for 6.03 GA and also 6.5 GA as it can lead to degenerate performance.*
             
            WORKS SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
            Enterprise Edition 6.0.0 build 1693 (Oct 11 2018 build) - I think this is GA 6.0.0
            Enterprise Edition 6.0.1 build 2037 (Jan 21 2019 build) - I think this is GA 6.0.1
            Enterprise Edition 6.0.2 build 2413 (Jun 12 2019 build) - I think this is GA 6.0.2
             
            WRONG BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION
            Enterprise Edition 6.0.3 build 2895 (Oct 09 2019 build) - I think this is GA 6.0.3
             
            WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
            Enterprise Edition 6.0.4 build 3082 (Feb 13 Build) - I think this is GA 6.0.4
             
            WRONG (AGAIN) BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION Enterprise Edition 6.5.0 build 4960 (Jan 13 Build) - I think this is GA 6.5
             Enterprise Edition 6.5.1 build 6120 (Feb 04 build)
             Enterprise Edition 6.5.1 build 6154 (Feb 11 build)
             Enterprise Edition 6.5.1 build 6168 (Feb 14 build)
             Enterprise Edition 6.5.1 build 6186 (Feb 20 build)
             Enterprise Edition 6.5.1 build 6191 (Feb 21 build)
             Enterprise Edition 6.5.1 build 6192 (Feb 22 build)
             Enterprise Edition 6.5.1 build 6193 (Feb 22 build)
             
            WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
            Enterprise Edition 6.5.1 build 6194 (Feb 24 build)
             *
             *
             Enterprise Edition 6.5.1 build 6218 (Feb 28 build)
             Enterprise Edition 6.5.1 build 6221 (Mar 02 build)
             
            Note: if you run my Eventing code "basic_index_path_issue.json" on versions 6.0.X (before 6.5) you must set var "ITER_CLOSE = false" as 6.0.X dose note support .close() on an iterable handle
             
            [~jon.strabala] reports:
              
             I simplified the handler and wrote a 12 page report to highlights the issue (see: TESTV2.zip)I was testing with a build that failed Enterprise Edition 6.5.1 build 6192 (Feb 22 build) - and there are a lot of builds that fail as per my list below:*We need to file a MB on this and also a release note for 6.03 GA and also 6.5 GA as it can lead to degenerate performance.*
              
             WORKS SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
             Enterprise Edition 6.0.0 build 1693 (Oct 11 2018 build) - I think this is GA 6.0.0
             Enterprise Edition 6.0.1 build 2037 (Jan 21 2019 build) - I think this is GA 6.0.1
             Enterprise Edition 6.0.2 build 2413 (Jun 12 2019 build) - I think this is GA 6.0.2
              
             WRONG BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION
             Enterprise Edition 6.0.3 build 2895 (Oct 09 2019 build) - I think this is GA 6.0.3
              
             WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
             Enterprise Edition 6.0.4 build 3082 (Feb 13 Build) - I think this is GA 6.0.4
              
             WRONG (AGAIN) BAD PERFORMANCE EVENTING HAS WRONG INDEX SELECTION
            Enterprise Edition 6.5.0 build 4960 (Jan 13 Build) - I think this is GA 6.5
             Enterprise Edition 6.5.1 build 6120 (Feb 04 build)
             Enterprise Edition 6.5.1 build 6154 (Feb 11 build)
             Enterprise Edition 6.5.1 build 6168 (Feb 14 build)
             Enterprise Edition 6.5.1 build 6186 (Feb 20 build)
             Enterprise Edition 6.5.1 build 6191 (Feb 21 build)
             Enterprise Edition 6.5.1 build 6192 (Feb 22 build)
             Enterprise Edition 6.5.1 build 6193 (Feb 22 build)
              
             WORKS (AGAIN) SELECTS PROPER INDEX SELECTION EVENTING PERFORMS LIKE QUERY WORKBENCH AND JAVA SDK
             Enterprise Edition 6.5.1 build 6194 (Feb 24 build)
             *
             *
             Enterprise Edition 6.5.1 build 6218 (Feb 28 build)
             Enterprise Edition 6.5.1 build 6221 (Mar 02 build)
              
             Note: if you run my Eventing code "basic_index_path_issue.json" on versions 6.0.X (before 6.5) you must set var "ITER_CLOSE = false" as 6.0.X dose note support .close() on an iterable handle
              
            Hide
            jon.strabala Jon Strabala added a comment -

            There is indeed a potential work around for the GA releases of 6.0.3 build 2895 and 6.5.0 build 4960 this issue given the two indices in this example when we load the `travel-sample` into a bucket it creates all it indices including:

             

            CREATE INDEX `def_type` ON `travel-sample`(`type`);
            

            Then we create our covering index (note `adv_airline_type_destinationairport` was created AFTER the above.)

             

            CREATE INDEX `adv_airline_type_destinationairport` ON `travel-sample`(`airline`,`destinationairport`) WHERE (`type` = "route");
            

            Here we don't use the newly made index and we crawl along at about 2 mutations per second.

            Now if we drop `def_type` and recreate it

             

            DROP INDEX `travel-sample`.`def_type`;
            CREATE INDEX `def_type` ON `travel-sample`(`type`);
            

            The covering index is now created before `adv_airline_type_destinationairport` the `def_type` and for some reason it is now selected and we now process 40X faster at about 80 mutations per second.

            If we reverse what we did the processing will slow down once again. 

            DROP INDEX `travel-sample`.`adv_airline_type_destinationairport`;
            CREATE INDEX `adv_airline_type_destinationairport` ON `travel-sample`(`airline`,`destinationairport`) WHERE (`type` = "route");
            

             Now we are back down to processing about 2 mutations per second.

            Thus if we have a customer using either GA releases of 6.0.3 build 2895 and 6.5.0 build 4960 and they are experiencing slow Evening performance on queries that they know work good in the Query work bench due to say custom covering indices.  If the customer DROPS all the other indices and re-creates them after the custom covering indices the performance should go back to what is expected.

             

            Show
            jon.strabala Jon Strabala added a comment - There is indeed a potential work around for the GA releases of 6.0.3 build 2895 and 6.5.0 build 4960 this issue given the two indices in this example when we load the `travel-sample` into a bucket it creates all it indices including:   CREATE INDEX `def_type` ON `travel-sample`(`type`); Then we create our covering index (note `adv_airline_type_destinationairport` was created AFTER the above.)   CREATE INDEX `adv_airline_type_destinationairport` ON `travel-sample`(`airline`,`destinationairport`) WHERE (`type` = "route"); Here we don't use the newly made index and we crawl along at about 2 mutations per second. Now if we drop `def_type` and recreate it   DROP INDEX `travel-sample`.`def_type`; CREATE INDEX `def_type` ON `travel-sample`(`type`); The covering index is now created before `adv_airline_type_destinationairport` the `def_type` and for some reason it is now selected and we now process 40X faster at about 80 mutations per second. If we reverse what we did the processing will slow down once again.  DROP INDEX `travel-sample`.`adv_airline_type_destinationairport`; CREATE INDEX `adv_airline_type_destinationairport` ON `travel-sample`(`airline`,`destinationairport`) WHERE (`type` = "route");  Now we are back down to processing about 2 mutations per second. Thus if we have a customer using either GA releases of 6.0.3 build 2895 and 6.5.0 build 4960 and they are experiencing slow Evening performance on queries that they know work good in the Query work bench due to say custom covering indices.  If the customer DROPS all the other indices and re-creates them after the custom covering indices the performance should go back to what is expected.  
            Hide
            siri Sriram Melkote (Inactive) added a comment -

            Another option is to explicitly choose index using the "USE INDEX" clause:

            https://docs.couchbase.com/server/4.1/n1ql/n1ql-language-reference/hints.html

            Show
            siri Sriram Melkote (Inactive) added a comment - Another option is to explicitly choose index using the "USE INDEX" clause: https://docs.couchbase.com/server/4.1/n1ql/n1ql-language-reference/hints.html
            wayne Wayne Siu made changes -
            Labels releasenote
            jeelan.poola Jeelan Poola made changes -
            Assignee Jeelan Poola [ jeelan.poola ] Amarantha Kulkarni [ amarantha.kulkarni ]
            Hide
            siri Sriram Melkote (Inactive) added a comment -

            Jon Strabala I don't think the order of creation matters. It picks up whatever indexes exist when it is deployed. Hence, I'd suggest modifying the note you recommend above to:

            Summary: N1QL generated from Eventing uses only indexes that were present at the time of deployment. Ensure all indexes necessary for queries utilized by the function has been created prior to deploying the function. This issue only impacts the GA releases of 6.0.3 build 2895 and 6.5.0 build 4960

            Show
            siri Sriram Melkote (Inactive) added a comment - Jon Strabala I don't think the order of creation matters. It picks up whatever indexes exist when it is deployed. Hence, I'd suggest modifying the note you recommend above to: Summary: N1QL generated from Eventing uses only indexes that were present at the time of deployment. Ensure all indexes necessary for queries utilized by the function has been created prior to deploying the function. This issue only impacts the GA releases of 6.0.3 build 2895 and 6.5.0 build 4960
            jeelan.poola Jeelan Poola made changes -
            Is this a Regression? Yes [ 10450 ] No [ 10451 ]
            Hide
            vikas.chaudhary Vikas Chaudhary added a comment -

            Amarantha Kulkarni can we close this

            Show
            vikas.chaudhary Vikas Chaudhary added a comment - Amarantha Kulkarni can we close this
            Hide
            jon.strabala Jon Strabala added a comment -

            Amarantha Kulkarni it really has to be a release note in https://docs.couchbase.com/server/6.5/release-notes/relnotes.html as it impacts two of our GA releases and creates unexpected behavior as per Sriram Melkote's update note.

            Show
            jon.strabala Jon Strabala added a comment - Amarantha Kulkarni  it really has to be a release note in https://docs.couchbase.com/server/6.5/release-notes/relnotes.html  as it impacts two of our GA releases and creates unexpected behavior as per Sriram Melkote 's update note.

              People

              Assignee:
              amarantha.kulkarni Amarantha Kulkarni (Inactive)
              Reporter:
              siri Sriram Melkote (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Gerrit Reviews

                  There are no open Gerrit changes

                    PagerDuty