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

Query: Correct index not used in unnest query

    XMLWordPrintable

Details

    Description

      Standard sample `beer-sample` bucket

      List of indexes:

       

      CREATE INDEX `adv_ALL_address` ON `beer-sample`((all (`address`)))

       

       

       

      CREATE PRIMARY INDEX `beer_primary` ON `beer-sample` WITH
      { "defer_build":true }
      

       

       

      CREATE INDEX `idx_addresses` ON `beer-sample`((all (`address`)))

      CREATE INDEX `idx_pairs` ON `beer-sample`((distinct (pairs(self))))

      CREATE INDEX `idx_suffixes` ON `beer-sample`((distinct (suffixes(`name`))))

      CREATE INDEX `idx_tokens` ON `beer-sample`((distinct (tokens(`description`))))

       

       

      execution plan for the following query

       

      explain select min(addr) from `beer-sample` unnest address as addr
      

      looks like this:

       

      {
        "plan": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IntersectScan",
              "scans": [
                {
                  "#operator": "DistinctScan",
                  "scan": {
                    "#operator": "IndexScan3",
                    "index": "idx_pairs",
                    "index_id": "dd8ead46b169b935",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "beer-sample",
                    "namespace": "default",
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "[successor(\"address\")]",
                            "inclusion": 1,
                            "low": "[\"address\", []]"
                          }
                        ]
                      }
                    ],
                    "using": "gsi"
                  }
                },
                {
                  "#operator": "DistinctScan",
                  "scan": {
                    "#operator": "IndexScan3",
                    "index": "idx_pairs",
                    "index_id": "dd8ead46b169b935",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "beer-sample",
                    "namespace": "default",
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "[\"address\", {}]",
                            "inclusion": 1,
                            "low": "[\"address\", false]"
                          }
                        ]
                      }
                    ],
                    "using": "gsi"
                  }
                }
              ]
            },
            {
              "#operator": "Fetch",
              "keyspace": "beer-sample",
              "namespace": "default"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Unnest",
                    "as": "addr",
                    "expr": "(`beer-sample`.`address`)"
                  }
                ]
              }
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "InitialGroup",
                    "aggregates": [
                      "min(`addr`)"
                    ],
                    "group_keys": []
                  }
                ]
              }
            },
            {
              "#operator": "IntermediateGroup",
              "aggregates": [
                "min(`addr`)"
              ],
              "group_keys": []
            },
            {
              "#operator": "FinalGroup",
              "aggregates": [
                "min(`addr`)"
              ],
              "group_keys": []
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "min(`addr`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        "text": "select min(addr) from `beer-sample` unnest address as addr"
      }

      So, idx_pairs index is involved

       

      The following query

       

      advise select min(addr) from `beer-sample` unnest address as addr
      

      gives this:

       

       

      [
        {
          "#operator": "Advise",
          "advice": {
            "#operator": "IndexAdvice",
            "adviseinfo": {
              "current_indexes": [
                {
                  "index_statement": "CREATE INDEX idx_pairs ON `beer-sample`((distinct (pairs(self))))",
                  "keyspace_alias": "beer-sample"
                }
              ],
              "recommended_indexes": {
                "indexes": [
                  {
                    "index_statement": "CREATE INDEX adv_ALL_address ON `beer-sample`(ALL `address`)",
                    "keyspace_alias": "beer-sample",
                    "recommending_rule": "Index keys follow order of predicate types: 1. leading array index for unnest."
                  }
                ]
              }
            }
          },
          "query": "select min(addr) from `beer-sample` unnest address as addr"
        }
      ]

      Since we already have recommended index - 

      CREATE INDEX `idx_addresses` ON `beer-sample`((all (`address`)))

      I assume, this index should be picked up.

       

       

      Attachments

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

        Activity

          People

            evgeny.makarenko Evgeny Makarenko (Inactive)
            evgeny.makarenko Evgeny Makarenko (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty