[MB-33009] N1QL plans different while using Named Parameters with Array Indexes - Couchbase database

XML

Word

Printable

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 6.5.0, 6.0.0, 5.5.0
Fix Version/s: 6.6.2, 7.0.0
Component/s: query
Labels:
- approved-for-6.6.2
- needs_automation

Triage:
Untriaged
Is this a Regression?:
Unknown

Description

When using named parameters, queries using "ANY ... SATISFIES ... END" produce different plans for execution that are not as optimised as compared to not using named parameters.

Using travel-sample, the following Index is used :

CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);

The following query which does not use a named parameter produces an expected query plan :

SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = "US681" END;

  "#operator": "Sequence",

  "#stats": {

    "#phaseSwitches": 1,

    "execTime": "926ns"

},

  "~children": [

      "#operator": "Authorize",

      "#stats": {

        "#phaseSwitches": 3,

        "execTime": "8.086µs",

        "servTime": "1.249702ms"

},

      "privileges": {

        "List": [

            "Target": "default:travel-sample",

            "Priv": 7

},

      "~child": {

        "#operator": "Sequence",

        "#stats": {

          "#phaseSwitches": 1,

          "execTime": "8.908µs"

},

        "~children": [

            "#operator": "DistinctScan",

            "#stats": {

              "#itemsIn": 38,

              "#itemsOut": 38,

              "#phaseSwitches": 157,

              "execTime": "91.318µs",

              "kernTime": "13.324669ms"

},

            "scan": {

              "#operator": "IndexScan3",

              "#stats": {

                "#itemsOut": 38,

                "#phaseSwitches": 157,

                "execTime": "124.857µs",

                "kernTime": "13.258µs",

                "servTime": "13.168046ms"

},

              "covers": [

                "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))",

                "cover ((meta(`travel-sample`).`id`))"

],

              "filter_covers": {

                "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)": true

},

              "index": "idx_flight_test",

              "index_id": "b6334268434aba20",

              "index_projection": {

                "primary_key": true

},

              "keyspace": "travel-sample",

              "namespace": "default",

              "spans": [

                  "exact": true,

                  "range": [

                      "high": "\"US681\"",

                      "inclusion": 3,

                      "low": "\"US681\""

],

              "using": "gsi",

              "#time_normal": "00:00.0132",

              "#time_absolute": 0.013292903000000002

},

            "#time_normal": "00:00.0000",

            "#time_absolute": 0.000091318

},

            "#operator": "Sequence",

            "#stats": {

              "#phaseSwitches": 1,

              "execTime": "6.068µs"

},

            "~children": [

                "#operator": "Filter",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 155,

                  "execTime": "3.446106ms",

                  "kernTime": "13.437146ms"

},

                "condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)",

                "#time_normal": "00:00.0034",

                "#time_absolute": 0.003446106

},

                "#operator": "InitialProject",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 119,

                  "execTime": "61.761µs",

                  "kernTime": "16.892771ms"

},

                "result_terms": [

                    "expr": "cover ((meta(`travel-sample`).`id`))"

],

                "#time_normal": "00:00.0000",

                "#time_absolute": 0.00006176100000000001

},

                "#operator": "FinalProject",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 115,

                  "execTime": "25.357µs",

                  "kernTime": "152.933µs"

},

                "#time_normal": "00:00.0000",

                "#time_absolute": 0.000025356999999999998

],

            "#time_normal": "00:00.0000",

            "#time_absolute": 0.000006067999999999999

],

        "#time_normal": "00:00.0000",

        "#time_absolute": 0.000008908

},

      "#time_normal": "00:00.0012",

      "#time_absolute": 0.0012577880000000001

},

      "#operator": "Stream",

      "#stats": {

        "#itemsIn": 38,

        "#itemsOut": 38,

        "#phaseSwitches": 155,

        "execTime": "5.46µs",

        "kernTime": "18.425336ms"

},

      "#time_normal": "00:00.0000",

      "#time_absolute": 0.00000546

],

  "~versions": [

    "2.0.0-N1QL",

    "5.5.2-3733-enterprise"

],

  "#time_normal": "00:00",

  "#time_absolute": 0

Using a named parameter however produces a different plan:

SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;

  "#operator": "Sequence",

  "#stats": {

    "#phaseSwitches": 1,

    "execTime": "12.611µs"

},

  "~children": [

      "#operator": "Authorize",

      "#stats": {

        "#phaseSwitches": 3,

        "execTime": "17.278µs",

        "servTime": "20.387192ms"

},

      "privileges": {

        "List": [

            "Target": "default:travel-sample",

            "Priv": 7

},

      "~child": {

        "#operator": "Sequence",

        "#stats": {

          "#phaseSwitches": 1,

          "execTime": "29.364µs"

},

        "~children": [

            "#operator": "DistinctScan",

            "#stats": {

              "#itemsIn": 38,

              "#itemsOut": 38,

              "#phaseSwitches": 157,

              "execTime": "121.912µs",

              "kernTime": "24.965667ms"

},

            "scan": {

              "#operator": "IndexScan3",

              "#stats": {

                "#itemsOut": 38,

                "#phaseSwitches": 157,

                "execTime": "79.143µs",

                "kernTime": "7.815µs",

                "servTime": "24.856622ms"

},

              "index": "idx_flight_test",

              "index_id": "b6334268434aba20",

              "index_projection": {

                "primary_key": true

},

              "keyspace": "travel-sample",

              "namespace": "default",

              "spans": [

                  "exact": true,

                  "range": [

                      "high": "\"US681\"",

                      "inclusion": 3,

                      "low": "\"US681\""

],

              "using": "gsi",

              "#time_normal": "00:00.0249",

              "#time_absolute": 0.024935765000000002

},

            "#time_normal": "00:00.0001",

            "#time_absolute": 0.00012191200000000001

},

            "#operator": "Fetch",

            "#stats": {

              "#itemsIn": 38,

              "#itemsOut": 38,

              "#phaseSwitches": 161,

              "execTime": "125.7µs",

              "kernTime": "25.113325ms",

              "servTime": "218.202109ms"

},

            "keyspace": "travel-sample",

            "namespace": "default",

            "#time_normal": "00:00.2183",

            "#time_absolute": 0.218327809

},

            "#operator": "Sequence",

            "#stats": {

              "#phaseSwitches": 1,

              "execTime": "7.403µs"

},

            "~children": [

                "#operator": "Filter",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 155,

                  "execTime": "9.705067ms",

                  "kernTime": "234.098171ms"

},

                "condition": "any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end",

                "#time_normal": "00:00.0097",

                "#time_absolute": 0.009705067

},

                "#operator": "InitialProject",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 119,

                  "execTime": "200.696µs",

                  "kernTime": "241.29278ms"

},

                "result_terms": [

                    "expr": "(meta(`travel-sample`).`id`)"

],

                "#time_normal": "00:00.0002",

                "#time_absolute": 0.000200696

},

                "#operator": "FinalProject",

                "#stats": {

                  "#itemsIn": 38,

                  "#itemsOut": 38,

                  "#phaseSwitches": 115,

                  "execTime": "17.587µs",

                  "kernTime": "2.347419ms"

},

                "#time_normal": "00:00.0000",

                "#time_absolute": 0.000017587

],

            "#time_normal": "00:00.0000",

            "#time_absolute": 0.000007402999999999999

],

        "#time_normal": "00:00.0000",

        "#time_absolute": 0.000029364000000000002

},

      "#time_normal": "00:00.0204",

      "#time_absolute": 0.020404469999999997

},

      "#operator": "Stream",

      "#stats": {

        "#itemsIn": 38,

        "#itemsOut": 38,

        "#phaseSwitches": 155,

        "execTime": "13.726µs",

        "kernTime": "264.321019ms"

},

      "#time_normal": "00:00.0000",

      "#time_absolute": 0.000013726

],

  "~versions": [

    "2.0.0-N1QL",

    "5.5.2-3733-enterprise"

],

  "#time_normal": "00:00.0000",

  "#time_absolute": 0.000012611

Attachments

Issue Links

causes

MB-52090 adhoc query index selection issue with LIKE as index condition and query parameters

Resolved

MB-48266 Array Index WHEN clause and query named parameters not working

Closed

MB-52161 [BP to 7.0.4,7.1.1] - adhoc query index selection issue with LIKE as index condition and query parameters

Closed

is duplicated by

MB-42038 Filter operator applied twice for adhoc query parameters

Closed

is triggering

MB-42956 aggregation regression

Closed

relates to

MB-36204 Query not covered when query parameter avialble

Closed

(1 relates to)

Gerrit Reviews

- Issue Only
- Show All Reviews
- Show Open Reviews
- Show All Issues
- Show Open Issues

For Gerrit Dashboard: MB-33009
#	Subject	Branch	Project	Status	CR	V
140398,3	MB-33009 Delay processing of query parameters till actual use	master	query	Status: MERGED	+2	+1
140399,2	MB-33009 Delay processing of query parameters till actual use	master	query-ee	Status: MERGED	+2	+1
141269,4	MB-42956 Replace query parameters for generation of index spans for IN and LIKE clauses	master	query	Status: MERGED	+2	+1
146582,2	MB-33009 Delay processing of query parameters till actual use	mad-hatter	query	Status: MERGED	+2	+1
146583,2	MB-33009 Delay processing of query parameters till actual use	mad-hatter	query-ee	Status: MERGED	+2	+1
160668,2	MB-48266 Replace query parameters for WHEN clause of ANY predicates	master	query	Status: MERGED	+2	+1
160669,2	MB-48266 Replace query parameters for WHEN clause of ANY predicates	cheshire-cat	query	Status: MERGED	+2	+1

Activity

Ascending order - Click to sort in descending order

Thomas Dotters created issue - 08/Feb/19 8:41 AM

Thomas Dotters made changes - 08/Feb/19 10:01 AM

Field	Original Value	New Value
Link		This issue blocks CBSE-6398 [ CBSE-6398 ]

Raju Suravarjjala made changes - 08/Feb/19 10:36 AM

Fix Version/s

Mad-Hatter [ 15037 ]

Sitaram Vemulapalli made changes - 08/Feb/19 3:30 PM

Issue Type

Bug [ 1 ]

Improvement [ 4 ]

Keshav Murthy made changes - 18/Mar/19 10:05 AM

Fix Version/s		feature-backlog [ 10342 ]
Fix Version/s	Mad-Hatter [ 15037 ]

Sitaram Vemulapalli made changes - 18/Feb/20 9:16 AM

Assignee

Keshav Murthy [ keshav ]

Kamini Jagtiani [ kamini.jagtiani ]

Fabrice Leray made changes - 09/Jun/20 4:06 AM

Link

This issue blocks CBSE-8464 [ CBSE-8464 ]

Tim Bradgate (Inactive) made changes - 24/Jun/20 9:05 AM

Affects Version/s

6.5.0 [ 15037 ]

Hitesh Walia made changes - 29/Jun/20 3:05 PM

Link

This issue blocks CBSE-8565 [ CBSE-8565 ]

Roi Katz made changes - 17/Aug/20 11:07 PM

Link

This issue blocks CBSE-8773 [ CBSE-8773 ]

Sitaram Vemulapalli made changes - 13/Oct/20 11:40 AM

Issue Type

Improvement [ 4 ]

Bug [ 1 ]

Sitaram Vemulapalli made changes - 13/Oct/20 11:40 AM

Fix Version/s		Cheshire-Cat [ 15915 ]
Fix Version/s	feature-backlog [ 10342 ]

Bingjie Miao made changes - 17/Nov/20 12:25 PM

Assignee	Kamini Jagtiani [ kamini.jagtiani ]	Mihir Kamdar [ mihir.kamdar ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Resolved [ 5 ]

Bingjie Miao made changes - 17/Nov/20 12:26 PM

Link

This issue is duplicated by ~~MB-42038~~ [ ~~MB-42038~~ ]

Sitaram Vemulapalli made changes - 30/Nov/20 9:45 AM

Link

This issue relates to ~~MB-36204~~ [ ~~MB-36204~~ ]

Bingjie Miao made changes - 01/Dec/20 12:26 PM

Link

This issue is triggering ~~MB-42956~~ [ ~~MB-42956~~ ]

Bingjie Miao made changes - 19/Feb/21 9:25 AM

Assignee	Mihir Kamdar [ mihir.kamdar ]	Bingjie Miao [ bingjie.miao ]
Resolution	Fixed [ 1 ]
Status	Resolved [ 5 ]	Reopened [ 4 ]

Raju Suravarjjala made changes - 19/Feb/21 10:14 AM

Link

This issue blocks MB-43310 [ MB-43310 ]

Sitaram Vemulapalli made changes - 19/Feb/21 11:28 AM

Link

This issue blocks CBSE-9705 [ CBSE-9705 ]

Bingjie Miao made changes - 19/Feb/21 4:37 PM

Assignee	Bingjie Miao [ bingjie.miao ]	Mihir Kamdar [ mihir.kamdar ]
Resolution		Fixed [ 1 ]
Status	Reopened [ 4 ]	Resolved [ 5 ]

Bingjie Miao made changes - 19/Feb/21 4:38 PM

Fix Version/s

6.6.2 [ 17103 ]

Pierre Regazzoni made changes - 19/Feb/21 5:28 PM

Assignee

Mihir Kamdar [ mihir.kamdar ]

Pierre Regazzoni [ JIRAUSER25157 ]

Pierre Regazzoni made changes - 19/Feb/21 5:31 PM

Status

Resolved [ 5 ]

Closed [ 6 ]

Wayne Siu made changes - 19/Feb/21 6:46 PM

Labels

approved-for-6.6.2

Lynn Straus made changes - 17/Jun/21 3:23 PM

Fix Version/s

7.0.0 [ 17233 ]

Lynn Straus made changes - 17/Jun/21 3:31 PM

Fix Version/s

Cheshire-Cat [ 15915 ]

Bingjie Miao made changes - 01/Sep/21 2:51 PM

Link

This issue causes ~~MB-42956~~ [ ~~MB-42956~~ ]

Bingjie Miao made changes - 01/Sep/21 2:51 PM

Link

This issue causes ~~MB-42956~~ [ ~~MB-42956~~ ]

Bingjie Miao made changes - 01/Sep/21 2:52 PM

Link

This issue causes ~~MB-48266~~ [ ~~MB-48266~~ ]

Mihir Kamdar (Inactive) made changes - 08/Feb/22 2:49 PM

Labels

approved-for-6.6.2

approved-for-6.6.2 needs_automation

Bingjie Miao made changes - 12/May/22 3:28 PM

Link

This issue causes ~~MB-52090~~ [ ~~MB-52090~~ ]

Bingjie Miao made changes - 16/May/22 10:00 AM

Link

This issue causes ~~MB-52161~~ [ ~~MB-52161~~ ]

People

Assignee:: Pierre Regazzoni

Reporter:: Thomas Dotters

Votes:: 2 Vote for this issue

Watchers:: 13 Start watching this issue

Dates

Created:: 08/Feb/19 8:41 AM

Updated:: 16/May/22 10:00 AM

Resolved:: 19/Feb/21 4:37 PM

Gerrit Reviews

There are no open Gerrit changes

Show There are 7 closed Gerrit changes

Hide There are 7 closed Gerrit changes

MB-33009 Delay processing of query parameters till actual use: Gerrit Review:

MB-33009 Delay processing of query parameters till actual use: Gerrit Review:

MB-42956 Replace query parameters for generation of index spans for IN and LIKE clauses: Gerrit Review:

MB-33009 Delay processing of query parameters till actual use: Gerrit Review:

MB-33009 Delay processing of query parameters till actual use: Gerrit Review:

MB-48266 Replace query parameters for WHEN clause of ANY predicates: Gerrit Review:

MB-48266 Replace query parameters for WHEN clause of ANY predicates: Gerrit Review:

Couchbase Server

N1QL plans different while using Named Parameters with Array Indexes

Details

Description

Attachments

Issue Links

Gerrit Reviews

Activity

People

Dates

Gerrit Reviews

PagerDuty