kernTime very long compared to other query benchmark parameters

I have a slight missunderstanding between my expectations of Couchbase query performance and reality, but especially some specific parameters when I do an EXPLAIN and peeak into the plan of that query. Namely I got a regular travel-sample bucket, nothing fancy there. I’ve added a simple index to it:

CREATE INDEX `def_country` ON `travel-sample`(`country`)

Now running a pretty simple query like this:

SELECT country, COUNT(*) FROM `travel-sample` GROUP BY country;

gives me

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "def_primary",
        "keyspace": "travel-sample",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "travel-sample",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(*)"
              ],
              "group_keys": [
                "(`travel-sample`.`country`)"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": [
          "(`travel-sample`.`country`)"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(*)"
        ],
        "group_keys": [
          "(`travel-sample`.`country`)"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "(`travel-sample`.`country`)"
                },
                {
                  "expr": "count(*)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT country, COUNT(*) FROM `travel-sample` GROUP BY country;"
}

and this

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 2,
    "execTime": "2.728µs",
    "kernTime": "593.943261ms"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "execTime": "5.416µs",
        "kernTime": "591.015119ms",
        "servTime": "2.88189ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:travel-sample",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 3,
          "execTime": "4.336µs",
          "kernTime": "591.006439ms"
        },
        "~children": [
          {
            "#operator": "PrimaryScan",
            "#stats": {
              "#itemsOut": 31591,
              "#phaseSwitches": 126367,
              "execTime": "59.901175ms",
              "kernTime": "495.567107ms",
              "servTime": "21.223821ms"
            },
            "index": "def_primary",
            "keyspace": "travel-sample",
            "namespace": "default",
            "using": "gsi",
            "#time_normal": "00:00.0811",
            "#time_absolute": 0.081124996
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 31591,
              "#itemsOut": 31591,
              "#phaseSwitches": 126495,
              "execTime": "38.970644ms",
              "kernTime": "52.476685ms",
              "servTime": "498.146292ms"
            },
            "keyspace": "travel-sample",
            "namespace": "default",
            "#time_normal": "00:00.5371",
            "#time_absolute": 0.537116936
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 3,
              "execTime": "81.6µs",
              "kernTime": "590.403294ms"
            },
            "copies": 6,
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 18,
                "execTime": "13.79µs",
                "kernTime": "3.539700556s"
              },
              "~children": [
                {
                  "#operator": "InitialGroup",
                  "#stats": {
                    "#itemsIn": 31591,
                    "#itemsOut": 24,
                    "#phaseSwitches": 63260,
                    "execTime": "617.396284ms",
                    "kernTime": "2.922209191s"
                  },
                  "aggregates": [
                    "count(*)"
                  ],
                  "group_keys": [
                    "(`travel-sample`.`country`)"
                  ],
                  "#time_normal": "00:00.6173",
                  "#time_absolute": 0.617396284
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000013789999999999998
            },
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.00008159999999999999
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 24,
              "#itemsOut": 4,
              "#phaseSwitches": 61,
              "execTime": "185.312µs",
              "kernTime": "590.502024ms"
            },
            "aggregates": [
              "count(*)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.000185312
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 4,
              "#itemsOut": 4,
              "#phaseSwitches": 21,
              "execTime": "39.269µs",
              "kernTime": "590.704876ms"
            },
            "aggregates": [
              "count(*)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000039269
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 5,
              "execTime": "112.872µs",
              "kernTime": "590.869401ms"
            },
            "copies": 6,
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 18,
                "execTime": "10.964µs",
                "kernTime": "3.542669702s"
              },
              "~children": [
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 4,
                    "#itemsOut": 4,
                    "#phaseSwitches": 34,
                    "execTime": "100.228µs",
                    "kernTime": "3.542359879s"
                  },
                  "result_terms": [
                    {
                      "expr": "(`travel-sample`.`country`)"
                    },
                    {
                      "expr": "count(*)"
                    }
                  ],
                  "#time_normal": "00:00.0001",
                  "#time_absolute": 0.00010022799999999999
                },
                {
                  "#operator": "FinalProject",
                  "#stats": {
                    "#itemsIn": 4,
                    "#itemsOut": 4,
                    "#phaseSwitches": 58,
                    "execTime": "18.179µs",
                    "kernTime": "3.542555127s"
                  },
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000018179
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000010964
            },
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.000112872
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.0000043360000000000005
      },
      "#time_normal": "00:00.0028",
      "#time_absolute": 0.0028873059999999996
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 4,
        "#itemsOut": 4,
        "#phaseSwitches": 15,
        "execTime": "3.533µs",
        "kernTime": "593.933895ms"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.000003533
    }
  ],
  "~versions": [
    "1.7.0-N1QL",
    "5.0.0-2873-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.0000027280000000000004
}

Overall it feels slow. Like a second to execute this simple query. And what is the most interesting in the result is the kernTime.

I am running here a docker image, both stable and beta sandbox, and the result is pretty much the same. Minimal difference if any. Server is pretty new E5, doing absolutely nothing else. But checked it on my poor, old laptop and it gives me similar, but even slower result. kernTime to blame and I do not get it…

Any ideas? At this point all of my team wants to go back to MongoDB, but I really, really hate their clustering approach…

You are using Group and aggregation and query needs to get all qualified items and do group and aggregation.
Also the query using PrimaryScan because there is no predicate.

Try the following query

SELECT country, COUNT(1) FROM travel-sample WHERE country is NOT MISSING GROUP BY country;

You can also checkout https://dzone.com/articles/count-amp-group-faster-using-n1ql
You should also try 5.5.0-DP , The following query uses Indexer Grouping and Aggregations
SELECT country, COUNT(1) FROM travel-sample WHERE country is NOT MISSING GROUP BY country;

This query gives me this:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 2,
    "execTime": "2.009µs",
    "kernTime": "633.827597ms"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "execTime": "19.164µs",
        "kernTime": "632.349182ms",
        "servTime": "1.425165ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:travel-sample",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 3,
          "execTime": "13.29µs",
          "kernTime": "632.331151ms"
        },
        "~children": [
          {
            "#operator": "PrimaryScan",
            "#stats": {
              "#itemsOut": 31591,
              "#phaseSwitches": 126367,
              "execTime": "62.292797ms",
              "kernTime": "538.48135ms",
              "servTime": "17.166531ms"
            },
            "index": "def_primary",
            "keyspace": "travel-sample",
            "namespace": "default",
            "using": "gsi",
            "#time_normal": "00:00.0794",
            "#time_absolute": 0.079459328
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 31591,
              "#itemsOut": 31591,
              "#phaseSwitches": 126495,
              "execTime": "32.099776ms",
              "kernTime": "47.439225ms",
              "servTime": "551.238827ms"
            },
            "keyspace": "travel-sample",
            "namespace": "default",
            "#time_normal": "00:00.5833",
            "#time_absolute": 0.5833386030000001
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 3,
              "execTime": "104.557µs",
              "kernTime": "631.805938ms"
            },
            "copies": 6,
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 18,
                "execTime": "14.716µs",
                "kernTime": "3.789869636s"
              },
              "~children": [
                {
                  "#operator": "Filter",
                  "#stats": {
                    "#itemsIn": 31591,
                    "#itemsOut": 7567,
                    "#phaseSwitches": 78334,
                    "execTime": "500.938082ms",
                    "kernTime": "3.288646527s"
                  },
                  "condition": "((`travel-sample`.`country`) is not missing)",
                  "#time_normal": "00:00.5009",
                  "#time_absolute": 0.500938082
                },
                {
                  "#operator": "InitialGroup",
                  "#stats": {
                    "#itemsIn": 7567,
                    "#itemsOut": 18,
                    "#phaseSwitches": 15212,
                    "execTime": "85.298845ms",
                    "kernTime": "3.704473828s"
                  },
                  "aggregates": [
                    "count(1)"
                  ],
                  "group_keys": [
                    "(`travel-sample`.`country`)"
                  ],
                  "#time_normal": "00:00.0852",
                  "#time_absolute": 0.085298845
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000014716
            },
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.00010455700000000001
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 18,
              "#itemsOut": 3,
              "#phaseSwitches": 47,
              "execTime": "122.746µs",
              "kernTime": "631.924204ms"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.000122746
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 3,
              "#itemsOut": 3,
              "#phaseSwitches": 17,
              "execTime": "27.035µs",
              "kernTime": "632.067451ms"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000027035
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 5,
              "execTime": "104.194µs",
              "kernTime": "632.200236ms"
            },
            "copies": 6,
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 18,
                "execTime": "13.493µs",
                "kernTime": "3.792350757s"
              },
              "~children": [
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 3,
                    "#itemsOut": 3,
                    "#phaseSwitches": 30,
                    "execTime": "85.8µs",
                    "kernTime": "3.792050208s"
                  },
                  "result_terms": [
                    {
                      "expr": "(`travel-sample`.`country`)"
                    },
                    {
                      "expr": "count(1)"
                    }
                  ],
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.0000858
                },
                {
                  "#operator": "FinalProject",
                  "#stats": {
                    "#itemsIn": 3,
                    "#itemsOut": 3,
                    "#phaseSwitches": 54,
                    "execTime": "11.968µs",
                    "kernTime": "3.792234165s"
                  },
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000011968
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000013493000000000001
            },
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.000104194
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.00001329
      },
      "#time_normal": "00:00.0014",
      "#time_absolute": 0.0014443289999999998
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 3,
        "#itemsOut": 3,
        "#phaseSwitches": 13,
        "execTime": "2.752µs",
        "kernTime": "633.81984ms"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.0000027519999999999997
    }
  ],
  "~versions": [
    "1.7.0-N1QL",
    "5.0.0-2873-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.0000020089999999999997
}

kernTime is pretty much the same for this.

BTW, is NOT MISSING will remove empty - it’s an important information in some queries! NULL is a value.

This is running on docker run -t --name couchbase -p 8091-8094:8091-8094 -p 11210:11210 couchbase/sandbox:5.0.0-beta which is what 5.5DB was suggesting as BETA. I will read into this, but I have to admit it was and is somewhat off-putting for the team. We tried multiple options of building those queries, all seems very sluggish. My additional problem is that we want to use spring-data and I am really afraid of the queries that this lib will generate for us. Overall we have a vote on Friday and something tells me most will vote in favor of MongoDB or PostgreSQL… Pity :frowning:

It looks like you have not created the secondary index

CREATE INDEX def_country ON travel-sample(country)

country IS NOT MISSING removes the documents that doesn’t have country field. It still keeps “country”:null fields

The following Book has details how the index selection works and how to design index for better performance.

So far, the grouping and aggregation in N1QL were relatively slow.
Many apps used couchbase views for larger grouping and aggregation.
https://blog.couchbase.com/comparing-couchbase-views-couchbase-n1ql-indexing/

As @vsr1 mentioned, we’ve improved the grouping and aggregation significantly.
See: https://developer.couchbase.com/documentation/server/5.5/n1ql/n1ql-language-reference/groupby-aggregate-performance.html

You can get the docker image for 5.5 developer build here:
https://hub.docker.com/r/couchbase/server/builds/bdwyzpz2jsdawwleuvy4xwj/

I fixed that, but it solves the problem only partially. A query with NOT MISSING got faster, but NULL is a value and without this condition, there is no improvement. I haven’t read into more details about indexes yet, but it seems you’re not indexing not existing fields - which is a huge difference compared to regular databases. I get the point and benefits of that on schema-less database, but there are plenty of usecases where NULL is a value as I mentioned before…

I think documentation has a mistake then, because Do a quick install still lists this as a docker call:

docker run -t --name db -p 8091-8094:8091-8094 -p 11210:11210 couchbase/sandbox:5.0.0-beta

I’ll look into the latest, 5.5.0-Mar tag then…

I am sorry, I just had a ROTFLOL moment.
In the latest, developer build GROUP BY as I used it before is even slower!

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "2.244µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "5.063µs",
        "servTime": "2.701614ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:travel-sample",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "5.778µs"
        },
        "~children": [
          {
            "#operator": "PrimaryScan3",
            "#stats": {
              "#itemsOut": 31591,
              "#phaseSwitches": 126367,
              "execTime": "112.433364ms",
              "kernTime": "1.541351755s",
              "servTime": "41.138052ms"
            },
            "index": "def_primary",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "travel-sample",
            "namespace": "default",
            "using": "gsi",
            "#time_normal": "00:00.1535",
            "#time_absolute": 0.153571416
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 31591,
              "#itemsOut": 31591,
              "#phaseSwitches": 130317,
              "execTime": "64.974007ms",
              "kernTime": "52.715458ms",
              "servTime": "1.591675938s"
            },
            "keyspace": "travel-sample",
            "namespace": "default",
            "#time_normal": "00:01.6566",
            "#time_absolute": 1.656649945
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "16.192µs"
            },
            "copies": 6,
            "~child": {
              "#operator": "InitialGroup",
              "#stats": {
                "#itemsIn": 31591,
                "#itemsOut": 24,
                "#phaseSwitches": 63248,
                "execTime": "642.739821ms",
                "kernTime": "9.613478872s"
              },
              "aggregates": [
                "count(1)"
              ],
              "group_keys": [
                "(`travel-sample`.`country`)"
              ],
              "#time_normal": "00:00.6427",
              "#time_absolute": 0.642739821
            },
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000016192
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 24,
              "#itemsOut": 4,
              "#phaseSwitches": 59,
              "execTime": "172.014µs",
              "kernTime": "1.709446394s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.000172014
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 4,
              "#itemsOut": 4,
              "#phaseSwitches": 19,
              "execTime": "44.356µs",
              "kernTime": "1.709622862s"
            },
            "aggregates": [
              "count(1)"
            ],
            "group_keys": [
              "(`travel-sample`.`country`)"
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000044356
          },
          {
            "#operator": "Parallel",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "31.871µs"
            },
            "copies": 6,
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 6,
                "execTime": "7.842µs"
              },
              "~children": [
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 4,
                    "#itemsOut": 4,
                    "#phaseSwitches": 42,
                    "execTime": "78.824µs",
                    "kernTime": "10.25797276s"
                  },
                  "result_terms": [
                    {
                      "expr": "(`travel-sample`.`country`)"
                    },
                    {
                      "expr": "count(1)"
                    }
                  ],
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000078824
                },
                {
                  "#operator": "FinalProject",
                  "#stats": {
                    "#itemsIn": 4,
                    "#itemsOut": 4,
                    "#phaseSwitches": 18,
                    "execTime": "6.162µs",
                    "kernTime": "77.333µs"
                  },
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000006162
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000007842
            },
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000031871
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.0000057779999999999996
      },
      "#time_normal": "00:00.0027",
      "#time_absolute": 0.002706677
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 4,
        "#itemsOut": 4,
        "#phaseSwitches": 19,
        "execTime": "4.655µs",
        "kernTime": "1.712534703s"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.000004655
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "5.5.0-2036-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000002244
}

Same machine, clean, not doing anything. Same query as it was still in the workbench as a history. Same indexes. Hmm…

fyi: 5.5 is still in developer preview, not GA yet.

You are not using right index. did you created the index mentioned earlier and query doesn’t have WHERE clause.

CREATE INDEX def_country ON `travel-sample`(country);
SELECT country, COUNT(1) FROM `travel-sample` WHERE country is NOT MISSING GROUP BY country;

5.5.0-DP on my machine(non-docker) get in 6ms

EXPLAIN SELECT country, COUNT(1) FROM `travel-sample` WHERE country is NOT MISSING GROUP BY country;
{
    "requestID": "badec3c5-770b-4e06-93ca-1ca713e9be67",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IndexScan3",
                        "covers": [
                            "cover ((`travel-sample`.`country`))",
                            "cover ((meta(`travel-sample`).`id`))",
                            "cover (count(1))"
                        ],
                        "index": "def_country",
                        "index_group_aggs": {
                            "aggregates": [
                                {
                                    "aggregate": "COUNT",
                                    "expr": "1",
                                    "id": 2,
                                    "keypos": -1
                                }
                            ],
                            "depends": [
                                0
                            ],
                            "group": [
                                {
                                    "depends": [
                                        0
                                    ],
                                    "expr": "cover ((`travel-sample`.`country`))",
                                    "id": 0,
                                    "keypos": 0
                                }
                            ]
                        },
                        "index_id": "91d58914d9fd2da3",
                        "index_projection": {
                            "entry_keys": [
                                0,
                                2
                            ]
                        },
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "spans": [
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "inclusion": 1,
                                        "low": "null"
                                    }
                                ]
                            }
                        ],
                        "using": "gsi"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "cover ((`travel-sample`.`country`))"
                                        },
                                        {
                                            "expr": "cover (count(1))"
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "SELECT country, COUNT(1) FROM `travel-sample` WHERE country is NOT MISSING GROUP BY country;"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.395055ms",
        "executionTime": "1.355535ms",
        "resultCount": 1,
        "resultSize": 3423
    }
}
cbq> SELECT country, COUNT(1) FROM `travel-sample` WHERE country is NOT MISSING GROUP BY country;
{
    "requestID": "41e1d853-27f7-475c-9f27-230059ad7ebb",
    "signature": {
        "$1": "number",
        "country": "json"
    },
    "results": [
        {
            "$1": 770,
            "country": "France"
        },
        {
            "$1": 2849,
            "country": "United Kingdom"
        },
        {
            "$1": 3948,
            "country": "United States"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "5.470498ms",
        "executionTime": "5.451095ms",
        "resultCount": 3,
        "resultSize": 215
    }
}

{"a":3, "b":null }

MISSING means the field is MISSING In above document if use c it returns MISSING
NULL means field is present and but value is null and undefined If use c it returns null

Couchbase Indexer will not index MISSING values if the leading index key evaluate to MISSING because the bucket can have different type of documents and fields may not present on all document
You can use partial index .


Check Designing Index for Query in Couchbase N1QL This explains more details.

Yes, I did. This is what I got on the Idexes page:

CREATE INDEX `def_country` ON `travel-sample`(`country`)

I do not see ANY difference in comparison to what you wrote as the index definition…

I absolutely do understand the logic of skipping NULL values! I wrote that above. To be honest it would be my first course of action as well as it simplifies a lot… if not everything.

And yet I think Couchbase should support it. Maybe it should be combined with additional WHERE discriminator clause specified during index creation, like (don’t read into details, just an idea):

CREATE INDEX `def_country` ON `travel-sample`(`country`) WHERE id LIKE 'airline_%';

Meaning that if I do know the type or any other fields to be of type or value, then NULL is suddenly a value as well. I think it would play nicely with the schemaless design but it would create some insight into a query in which I need to know in how many objects of type A there is a value missing for property AX. From the index building perspective it should also be very efficient and easy to implement, since NULL is and can be a value in such. Catching my drift here?

I think, in all honesty, expected to see this since the assumption I made starting to test Couchbase. Maybe you never thought of it or maybe you did and discarded this idea for any reason there was or is. Don’t read much into this idea if that is the case. Otherwise… maybe a nice feature request?

Couhbase Doesn’t skip any NULL values. Indexer doesn’t index Only MISSING value on leading index key. If query wants in that situations we use primary index.

N1QL already supports this.

CREATE INDEX def_country ON travel-sample(country) WHERE id LIKE ‘airline_%’;
As above index has partial data from the bucket query requires to have sub set index where condition. Otherwise the index will not qualify for scan (if used it will result in the wrong results.

SELECT country, COUNT(1) FROM travel-sample WHERE country is NOT MISSING AND id LIKE ‘airline_%’ GROUP BY country;

If you want filter based on document key use META().id LIKE “airline_%”, META().id gives document key

The following link has different type of indexes.

https://developer.couchbase.com/documentation/server/current/performance/indexing-and-query-perf.html

Now we’re talking! I definetely have to read into those docs more…

An update - did my share of learning and adjusted my expectations and queries. It’s better. I am still missing some features, but we can go around them… mostly. Not all of them, but then no DB can.