morrie
December 20, 2015, 3:02pm
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?
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.
1 Like
morrie
December 21, 2015, 8:24am
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
@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!