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

N1QL plans different while using Named Parameters with Array Indexes

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 6.5.0, 6.0.0, 5.5.0
    • 6.6.2, 7.0.0
    • query
    • 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

            Build couchbase-server-7.0.0-3772 contains query-ee commit 273b156 with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-3772 contains query-ee commit 273b156 with commit message: MB-33009 Delay processing of query parameters till actual use
            bingjie.miao Bingjie Miao added a comment -

            Reopen for 6.6.2.

            bingjie.miao Bingjie Miao added a comment - Reopen for 6.6.2.

            Build couchbase-server-6.6.2-9529 contains query commit 7383dfb with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9529 contains query commit 7383dfb with commit message: MB-33009 Delay processing of query parameters till actual use

            Build couchbase-server-6.6.2-9529 contains query-ee commit e64e499 with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9529 contains query-ee commit e64e499 with commit message: MB-33009 Delay processing of query parameters till actual use

            Verified on 6.6.2-9529 and 7.0.0-4485

            cbq> explain SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;
            {
                "requestID": "c6b8ce3e-83a5-4f03-92c6-e431d530c716",
                "signature": "json",
                "results": [
                {
                    "plan": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan3",
                                    "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`) = $flight) end)": true
                                    },
                                    "index": "idx_flight_test",
                                    "index_id": "80b731d88b5474e1",
                                    "index_projection": {
                                        "primary_key": true
                                    },
                                    "keyspace": "travel-sample",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "exact": true,
                                            "range": [
                                                {
                                                    "high": "$flight",
                                                    "inclusion": 3,
                                                    "low": "$flight"
                                                }
                                            ]
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Filter",
                                            "condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "cover ((meta(`travel-sample`).`id`))"
                                                }
                                            ]
                                        },
                                        {
                                            "#operator": "FinalProject"
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    "text": "SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "3.438649ms",
                    "executionTime": "3.179783ms",
                    "resultCount": 1,
                    "resultSize": 2714
                }
            } 

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 6.6.2-9529 and 7.0.0-4485 cbq> explain SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END; {     "requestID" : "c6b8ce3e-83a5-4f03-92c6-e431d530c716" ,     "signature" : "json" ,     "results" : [     {         "plan" : {             "#operator" : "Sequence" ,             "~children" : [                 {                     "#operator" : "DistinctScan" ,                     "scan" : {                         "#operator" : "IndexScan3" ,                         "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`) = $flight) end)" : true                         },                         "index" : "idx_flight_test" ,                         "index_id" : "80b731d88b5474e1" ,                         "index_projection" : {                             "primary_key" : true                         },                         "keyspace" : "travel-sample" ,                         "namespace" : "default" ,                         "spans" : [                             {                                 "exact" : true ,                                 "range" : [                                     {                                         "high" : "$flight" ,                                         "inclusion" : 3 ,                                         "low" : "$flight"                                     }                                 ]                             }                         ],                         "using" : "gsi"                     }                 },                 {                     "#operator" : "Parallel" ,                     "~child" : {                         "#operator" : "Sequence" ,                         "~children" : [                             {                                 "#operator" : "Filter" ,                                 "condition" : "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)"                             },                             {                                 "#operator" : "InitialProject" ,                                 "result_terms" : [                                     {                                         "expr" : "cover ((meta(`travel-sample`).`id`))"                                     }                                 ]                             },                             {                                 "#operator" : "FinalProject"                             }                         ]                     }                 }             ]         },         "text" : "SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "3.438649ms" ,         "executionTime" : "3.179783ms" ,         "resultCount" : 1 ,         "resultSize" : 2714     } }

            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