Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Fixed
Priority: Major
Fix Version/s: 6.6.2, 7.0.0
Affects Version/s: 5.5.0, 6.0.0, 6.5.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

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

Activity

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:

N1QL plans different while using Named Parameters with Array Indexes

Details

Description

Attachments

Issue Links

Gerrit Reviews

Activity

People

Dates

Gerrit Reviews

PagerDuty