N1QL - Aggregates and Indexes


#1

I have created the following index on standard beer-sample:

CREATE INDEX beer-sample-country-idx ON beer-sample(country) USING GSI;

The following query uses this index according to the explain plan and in fact the index is a covering index for this query :

SELECT count (country) from beer-sample;

The following query does not use this index according to the explain plan

SELECT country, count (*) from beer-sample group by country;

Why does this second query not use the index when the same index should still be seen as a covering index?


#2

Hi,

The following query should use a covering index:

The WHERE clause is needed because MISSING values are not indexed, whereas your aggregate query generates a special group for MISSING values.


#3

Confirmed:

SELECT country, count (*)
FROM beer-sample
WHERE country IS NOT MISSING
GROUP BY country;

does use the index as a covering index.

Is this problem documented somewhere?

Thanks


#4

@morrie, we will document this under covering indexes. SQL does not have to deal with MISSING, which is why this case is specific to N1QL. Otherwise, we try to act like SQL and to avoid surprises. And our users are also quite vigilant! :slight_smile: