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

[BP to 7.0.4] - JOIN query not covered

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Unknown

    Description

      DROP INDEX default.ix11;
      DROP INDEX default.ix12;
      CREATE INDEX ix11 ON `default` ( ALL ARRAY item.n FOR item IN items WHEN item.type = "al" END) WHERE type = "ll";
      CREATE INDEX ix12 ON `default` ( l , n, v) WHERE type = "rr";
      EXPLAIN SELECT f.*
      FROM ( SELECT item.n, "" AS v
             FROM default AS o
             UNNEST o.items AS item
             WHERE  item.n = "" AND o.type = "ll" AND item.type = "al" ) AS f
      JOIN default AS c
      ON f.n = c.n AND f.v = c.v AND c.type = "rr" AND c.l = "l";
      

       EXPLAIN SELECT f.* FROM ( SELECT item.n, "" AS v FROM default AS o UNNEST o.items AS item WHERE  item.n = "" AND o.type = "ll" AND item.type = "al" ) AS f JOIN default AS c ON f.n = c.n AND f.v = c.v AND c.type = "rr" AND c.l = "l";
      {
          "requestID": "f8abaafd-6134-4ee0-9b5a-bffbb3f8c6b6",
          "signature": "json",
          "results": [
          {
              "plan": {
                  "#operator": "Sequence",
                  "~children": [
                      {
                          "#operator": "Sequence",
                          "~children": [
                              {
                                  "#operator": "IndexScan3",
                                  "as": "o",
                                  "covers": [
                                      "cover ((`item`.`n`))",
                                      "cover ((meta(`o`).`id`))"
                                  ],
                                  "filter": "((cover ((`o`.`type`)) = \"ll\") and cover (is_array((`o`.`items`))))",
                                  "filter_covers": {
                                      "cover (((`o`.`items`) \u003c {}))": true,
                                      "cover (([] \u003c= (`o`.`items`)))": true,
                                      "cover ((`item`.`type`))": "al",
                                      "cover ((`o`.`type`))": "ll",
                                      "cover (is_array((`o`.`items`)))": true
                                  },
                                  "index": "ix11",
                                  "index_id": "c823ac0cd3e2e627",
                                  "keyspace": "default",
                                  "namespace": "default",
                                  "spans": [
                                      {
                                          "exact": true,
                                          "range": [
                                              {
                                                  "high": "\"\"",
                                                  "inclusion": 3,
                                                  "index_key": "(all (array (`item`.`n`) for `item` in `items` when ((`item`.`type`) = \"al\") end))",
                                                  "low": "\"\""
                                              }
                                          ]
                                      }
                                  ],
                                  "using": "gsi"
                              },
                              {
                                  "#operator": "Parallel",
                                  "~child": {
                                      "#operator": "Sequence",
                                      "~children": [
                                          {
                                              "#operator": "Filter",
                                              "condition": "((cover ((`item`.`n`)) = \"\") and (cover ((`item`.`type`)) = \"al\"))"
                                          },
                                          {
                                              "#operator": "InitialProject",
                                              "result_terms": [
                                                  {
                                                      "expr": "cover ((`item`.`n`))"
                                                  },
                                                  {
                                                      "as": "v",
                                                      "expr": "\"\""
                                                  }
                                              ]
                                          }
                                      ]
                                  }
                              }
                          ]
                      },
                      {
                          "#operator": "Alias",
                          "as": "f"
                      },
                      {
                          "#operator": "Parallel",
                          "~child": {
                              "#operator": "Sequence",
                              "~children": [
                                  {
                                      "#operator": "NestedLoopJoin",
                                      "alias": "c",
                                      "on_clause": "(((((`f`.`n`) = (`c`.`n`)) and ((`f`.`v`) = (`c`.`v`))) and ((`c`.`type`) = \"rr\")) and ((`c`.`l`) = \"l\"))",
                                      "~child": {
                                          "#operator": "Sequence",
                                          "~children": [
                                              {
                                                  "#operator": "IndexScan3",
                                                  "as": "c",
                                                  "index": "ix12",
                                                  "index_id": "543c3714c85d3138",
                                                  "index_projection": {
                                                      "primary_key": true
                                                  },
                                                  "keyspace": "default",
                                                  "namespace": "default",
                                                  "nested_loop": true,
                                                  "spans": [
                                                      {
                                                          "exact": true,
                                                          "range": [
                                                              {
                                                                  "high": "\"l\"",
                                                                  "inclusion": 3,
                                                                  "index_key": "`l`",
                                                                  "low": "\"l\""
                                                              },
                                                              {
                                                                  "high": "(`f`.`n`)",
                                                                  "inclusion": 3,
                                                                  "index_key": "`n`",
                                                                  "low": "(`f`.`n`)"
                                                              },
                                                              {
                                                                  "high": "(`f`.`v`)",
                                                                  "inclusion": 3,
                                                                  "index_key": "`v`",
                                                                  "low": "(`f`.`v`)"
                                                              }
                                                          ]
                                                      }
                                                  ],
                                                  "using": "gsi"
                                              },
                                              {
                                                  "#operator": "Fetch",
                                                  "as": "c",
                                                  "keyspace": "default",
                                                  "namespace": "default",
                                                  "nested_loop": true
                                              },
                                              {
                                                  "#operator": "Parallel",
                                                  "~child": {
                                                      "#operator": "Sequence",
                                                      "~children": [
                                                          {
                                                              "#operator": "Filter",
                                                              "condition": "(((`c`.`type`) = \"rr\") and ((`c`.`l`) = \"l\"))"
                                                          }
                                                      ]
                                                  }
                                              }
                                          ]
                                      }
                                  },
                                  {
                                      "#operator": "InitialProject",
                                      "result_terms": [
                                          {
                                              "expr": "`f`",
                                              "star": true
                                          }
                                      ]
                                  }
                              ]
                          }
                      }
                  ]
              },
              "text": "SELECT f.* FROM ( SELECT item.n, \"\" AS v FROM default AS o UNNEST o.items AS item WHERE  item.n = \"\" AND o.type = \"ll\" AND item.type = \"al\" ) AS f JOIN default AS c ON f.n = c.n AND f.v = c.v AND c.type = \"rr\" AND c.l = \"l\";"
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "9.399934ms",
              "executionTime": "9.300051ms",
              "resultCount": 1,
              "resultSize": 8447,
              "serviceLoad": 2
          }
      }
      

      Why right side of JOIN (c) is not covered?
      If we remove item.type = "al" from index and query removed it covers. Interesting thing is FROM subquery where index is used is covered not sure why that impacting outside query.

      Attachments

        Issue Links

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

          Activity

            People

              ajay.bhullar Ajay Bhullar
              bingjie.miao Bingjie Miao
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty