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

N1QL plans different while using Named Parameters with Array Indexes

    XMLWordPrintable

Details

    • Untriaged
    • 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

          For Gerrit Dashboard: MB-33009
          # Subject Branch Project Status CR V

          Activity

            thomas.dotters Thomas Dotters created issue -
            thomas.dotters Thomas Dotters made changes -
            Field Original Value New Value
            Link This issue blocks CBSE-6398 [ CBSE-6398 ]
            raju Raju Suravarjjala made changes -
            Fix Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Issue Type Bug [ 1 ] Improvement [ 4 ]
            keshav Keshav Murthy made changes -
            Fix Version/s feature-backlog [ 10342 ]
            Fix Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Keshav Murthy [ keshav ] Kamini Jagtiani [ kamini.jagtiani ]
            fabrice.leray Fabrice Leray made changes -
            Link This issue blocks CBSE-8464 [ CBSE-8464 ]
            tim.bradgate Tim Bradgate (Inactive) made changes -
            Affects Version/s 6.5.0 [ 15037 ]
            hitesh.walia Hitesh Walia made changes -
            Link This issue blocks CBSE-8565 [ CBSE-8565 ]
            roi.katz Roi Katz made changes -
            Link This issue blocks CBSE-8773 [ CBSE-8773 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Issue Type Improvement [ 4 ] Bug [ 1 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Fix Version/s Cheshire-Cat [ 15915 ]
            Fix Version/s feature-backlog [ 10342 ]
            bingjie.miao Bingjie Miao made changes -
            Assignee Kamini Jagtiani [ kamini.jagtiani ] Mihir Kamdar [ mihir.kamdar ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Resolved [ 5 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue is duplicated by MB-42038 [ MB-42038 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue relates to MB-36204 [ MB-36204 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue is triggering MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao made changes -
            Assignee Mihir Kamdar [ mihir.kamdar ] Bingjie Miao [ bingjie.miao ]
            Resolution Fixed [ 1 ]
            Status Resolved [ 5 ] Reopened [ 4 ]
            raju Raju Suravarjjala made changes -
            Link This issue blocks MB-43310 [ MB-43310 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue blocks CBSE-9705 [ CBSE-9705 ]
            bingjie.miao Bingjie Miao made changes -
            Assignee Bingjie Miao [ bingjie.miao ] Mihir Kamdar [ mihir.kamdar ]
            Resolution Fixed [ 1 ]
            Status Reopened [ 4 ] Resolved [ 5 ]
            bingjie.miao Bingjie Miao made changes -
            Fix Version/s 6.6.2 [ 17103 ]
            pierre.regazzoni Pierre Regazzoni made changes -
            Assignee Mihir Kamdar [ mihir.kamdar ] Pierre Regazzoni [ JIRAUSER25157 ]
            pierre.regazzoni Pierre Regazzoni made changes -
            Status Resolved [ 5 ] Closed [ 6 ]
            wayne Wayne Siu made changes -
            Labels approved-for-6.6.2
            lynn.straus Lynn Straus made changes -
            Fix Version/s 7.0.0 [ 17233 ]
            lynn.straus Lynn Straus made changes -
            Fix Version/s Cheshire-Cat [ 15915 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-48266 [ MB-48266 ]
            mihir.kamdar Mihir Kamdar (Inactive) made changes -
            Labels approved-for-6.6.2 approved-for-6.6.2 needs_automation
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-52090 [ MB-52090 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-52161 [ MB-52161 ]

            People

              pierre.regazzoni Pierre Regazzoni
              thomas.dotters Thomas Dotters
              Votes:
              2 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty