Interesting Index Behavior

SQL++
1

I was trying to run a simple query that gets me the count of doc _types in my bucket. I have an Index of _type so i expected this to be a fast thing, boy was i wrong. even if i say USE INDEX(_type) it still uses primary

select _type, count(*) from Contacts
group by _type

Even so i have the _type index it does not use it here is the plan text on the other hand if i run the below query it uses the _type index

select _type from Contacts
where _type = 'relationship'

Here is the Plan of the group query

{
  "#operator": "Authorize",
  "#stats": {
    "#phaseSwitches": 4,
    "execTime": "8.469µs",
    "servTime": "33.58µs"
  },
  "privileges": {
    "List": [
      {
        "Target": "default:Contacts",
        "Priv": 7,
        "Props": 0
      }
    ]
  },
  "~child": {
    "#operator": "Sequence",
    "#stats": {
      "#phaseSwitches": 2,
      "execTime": "9.199µs"
    },
    "~children": [
      {
        "#operator": "PrimaryScan3",
        "#stats": {
          "#heartbeatYields": 1,
          "#itemsOut": 206072,
          "#phaseSwitches": 824291,
          "execTime": "1.160198899s",
          "kernTime": "14.517833538s",
          "servTime": "661.966305ms"
        },
        "index": "primary",
        "index_projection": {
          "primary_key": true
        },
        "keyspace": "Contacts",
        "namespace": "default",
        "using": "gsi",
        "#time_normal": "00:01.822",
        "#time_absolute": 1.822165204
      },
      {
        "#operator": "Fetch",
        "#stats": {
          "#heartbeatYields": 1435,
          "#itemsIn": 206072,
          "#itemsOut": 206072,
          "#phaseSwitches": 825168,
          "execTime": "1.440984795s",
          "kernTime": "1.089699196s",
          "servTime": "13.809351261s"
        },
        "keyspace": "Contacts",
        "namespace": "default",
        "#time_normal": "00:15.250",
        "#time_absolute": 15.250336056
      },
      {
        "#operator": "InitialGroup",
        "#stats": {
          "#heartbeatYields": 1,
          "#itemsIn": 206072,
          "#itemsOut": 58,
          "#phaseSwitches": 412264,
          "execTime": "4.667824662s",
          "kernTime": "11.672625531s"
        },
        "aggregates": [
          "count(*)"
        ],
        "group_keys": [
          "(`Contacts`.`_type`)"
        ],
        "#time_normal": "00:04.667",
        "#time_absolute": 4.667824662
      },
      {
        "#operator": "IntermediateGroup",
        "#stats": {
          "#heartbeatYields": 2,
          "#itemsIn": 58,
          "#itemsOut": 58,
          "#phaseSwitches": 236,
          "execTime": "924.897µs",
          "kernTime": "16.340752977s"
        },
        "aggregates": [
          "count(*)"
        ],
        "group_keys": [
          "(`Contacts`.`_type`)"
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000924897
      },
      {
        "#operator": "FinalGroup",
        "#stats": {
          "#heartbeatYields": 1,
          "#itemsIn": 58,
          "#itemsOut": 58,
          "#phaseSwitches": 236,
          "execTime": "975.763µs",
          "kernTime": "16.341559228s"
        },
        "aggregates": [
          "count(*)"
        ],
        "group_keys": [
          "(`Contacts`.`_type`)"
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000975763
      },
      {
        "#operator": "InitialProject",
        "#stats": {
          "#itemsIn": 58,
          "#itemsOut": 58,
          "#phaseSwitches": 179,
          "execTime": "1.488217ms",
          "kernTime": "16.342381216s",
          "state": "running"
        },
        "result_terms": [
          {
            "expr": "(`Contacts`.`_type`)"
          },
          {
            "expr": "count(*)"
          }
        ],
        "#time_normal": "00:00.001",
        "#time_absolute": 0.0014882169999999998
      },
      {
        "#operator": "Stream",
        "#stats": {
          "#itemsIn": 58,
          "#itemsOut": 58,
          "#phaseSwitches": 59,
          "execTime": "1.018463ms"
        },
        "#time_normal": "00:00.001",
        "#time_absolute": 0.0010184629999999998
      }
    ],
    "#time_normal": "00:00.000",
    "#time_absolute": 0.000009199
  },
  "~versions": [
    "7.1.0-N1QL",
    "7.1.0-2556-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000042049000000000003
}
2

Index selection is based on predicate

select _type, count(*) from Contacts
WHERE _type IS NOT MISSING
group by _type
3

Even so all docs have the _type and requires the IS NOT Missing part…
I thought even so a manual USE INDEX would let me specify the _type index.

4

It is required. USE INDEX is hint (It will validate if the index is qualified or not)

1 Like