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

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

          Activity

            No work has yet been logged on this issue.

            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