Index not hit on very simple ORDER BY N1QL query

SQL++
1

Dear all,

I’m asking you for support for a very simple (i think) question about indexes and order by clause.
I have this index:

CREATE INDEX autocomplete_ranking_node31 ON products(autocompleteMacroRankingPosition) using gsi;

If I run the following query:

explain select * from products ORDER BY autocompleteMacroRankingPosition ASC limit 7;

I get a plan like this (PrimaryScan)

"plan": {
  "#operator": "Sequence",
  "~children": [
    {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "products",
          "namespace": "default",
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "products",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "self",
                    "star": true
                  }
                ]
              }
            ]
          }
        }
      ]
    },
    {
      "#operator": "Order",
      "limit": "7",
      "sort_terms": [
        {
          "expr": "(`products`.`autocompleteMacroRankingPosition`)"
        }
      ]
    },
    {
      "#operator": "Limit",
      "expr": "7"
    },
    {
      "#operator": "FinalProject"
    }
  ]
},
"text": "select * from products ORDER BY autocompleteMacroRankingPosition ASC limit 7;"

Even with USE INDEX I can’t get the query use the highlighted index.

explain select * from products USE INDEX (autocomplete_ranking_node31 USING GSI) ORDER BY autocompleteMacroRankingPosition ASC limit 7;

I’m running CouchBase v4.6.3 EE

Thank you,

Stefano

2

To pick the index leading key of index needs to be part of query predicate.
Try the following.

explain select * from products WHERE autocompleteMacroRankingPosition IS NOT MISSING ORDER BY autocompleteMacroRankingPosition ASC limit 7;

3

Thank you @vsr1,

This solved the problem. I’m applying this logic to our more complicated query in the following days.

Regards,

Stefano

4

The following guide has details of various optimization suggestions https://dzone.com/articles/n1ql-a-practicle-guide .

1 Like