Creating indices based on suggestions from the index-advisor not working?

zno · February 21, 2020, 9:59am

Couchbase Server 6.0
2,013,261 documents

The explain json output for the query
https://pastebin.com/Yz7fWDLc

My input into the index-advisor
https://index-advisor.couchbase.com/

  advise 
    SELECT meta().id, type, projectID, sinceEpoch, updatedAt, spaceCategoryName, spaceName, spaceNrList, unitName, unitNrList, objectName, objectNrList, detailName, detailNrList, proximusUnitName, proximusUnitNrList, proximusObjectName, proximusObjectNrList, errorCodeName, responsibleParties, remedyStates, inspectionObjectID, inspectionObjectTypeName, inspectionObjectNumber, specialCircumstanceName, specialCircumstanceAcronym, selfRegulationID, selfRegulationTypeName, photoIDs
    FROM `ispect` as D1 
    LET state = (SELECT RAW MAX([S1.inspectionNumber,S1])[1] FROM D1.remedyStates AS S1)[0], docId = SUBSTR(meta(D1).id, 0, 5)
    WHERE docId != '_sync' 
    AND type = 'defect' 
    AND (isDeleted is not valued OR isDeleted == false) 
    AND projectID = '1321231' 
    AND state.inspectionID IN ['123123']
    AND inspectionObjectID IN ['123123']
    AND responsibleParties IS VALUED 
    AND responsibleParties[0].documentID IN ['123123']
    AND spaceName IS VALUED AND spaceName IN ['123123']
    AND specialCircumstanceName = 'Beställare'
    AND errorCodeName != '-'
    AND state.isRemedied = true
    AND state.remedyStatus[0].taggedAsRemediedBySubcontractor = true
    AND state.isRemedied = false 
    AND (state.remedyStatus IS NOT VALUED OR state.remedyStatus[0].taggedAsRemediedBySubcontractor = false)

The index-advisor output for the 'covering index’
CREATE INDEX adv_type_specialCircumstanceName_projectID_inspectionObjectID_sp262075568 ON ispect(type,specialCircumstanceName,projectID,inspectionObjectID,spaceName,responsibleParties[0].documentID,errorCodeName,substr0((meta(self).id), 0, 5),responsibleParties,isDeleted,spaceNrList,proximusUnitName,proximusObjectNrList,inspectionObjectNumber,selfRegulationTypeName,detailName,unitNrList,detailNrList,selfRegulationID,proximusUnitNrList,inspectionObjectTypeName,unitName,updatedAt,sinceEpoch,objectName,objectNrList,proximusObjectName,photoIDs,remedyStates,spaceCategoryName,specialCircumstanceAcronym)

The query with USE INDEX takes 2 minutes!
SELECT meta().id, type, projectID, sinceEpoch, updatedAt, spaceCategoryName, spaceName, spaceNrList, unitName, unitNrList, objectName, objectNrList, detailName, detailNrList, proximusUnitName, proximusUnitNrList, proximusObjectName, proximusObjectNrList, errorCodeName, responsibleParties, remedyStates, inspectionObjectID, inspectionObjectTypeName, inspectionObjectNumber, specialCircumstanceName, specialCircumstanceAcronym, selfRegulationID, selfRegulationTypeName, photoIDs FROM ispect as D1 USE INDEX (issue_filter_index USING GSI) LET state = (SELECT RAW MAX([S1.inspectionNumber,S1])[1] FROM D1.remedyStates AS S1)[0], docId = SUBSTR(meta(D1).id, 0, 5) WHERE type = 'defect' AND projectID = 'project_1503412693_b208a02b-ce84-438a-bdf9-3e3ab28edd08' AND docId != '_sync' AND (isDeleted is not valued OR isDeleted == false) AND state.isRemedied = false AND (state.remedyStatus IS NOT VALUED OR state.remedyStatus[0].taggedAsRemediedBySubcontractor = false)

The query without USING takes 2 seconds, it falls back on the type_projectId_Idx
CREATE INDEX type_projectId_Idx ON ispect(type,projectID) WHERE ((type is valued) and (projectID is valued))

The result set is around 2000 records, the total amount of documents in the bucket is 2 million. Most of my n1ql queries finish in under 40ms.

Why isn’t the ‘covering index’ from the index-advisor working for my query? It’s faster to use a more narrow index than the ‘covering index’, that seems a bit counter-intuitive.

vsr1 · February 21, 2020, 1:43pm

It is all depends on data. Also you have so many keys in index and index is so wide so index size is too big.
In your case advisor gave index on Q1 and it works fine. When You ran Q2 it is used that index because it is covered vs other index not covered. But covered index is suboptimal because there is predicate specialCircumstanceName is missing in Q2. You can run both queries and get indexes do manual merge or rearrange keys.(Advise is single query, Advisor (https://blog.couchbase.com/index-advisor-for-query-workload/) which does workload . Consolidate indexes is Future enhancement).

You have partial index. Index Advisor can’t recommend partial index with out data.

Best way is run the index advisor on the cluster where data is present. As it is rule based check the recommended indexes check which performs better. Will you able to provide sample document.

Try following non-covered index and query (index will have predicate portion of the expressions)

  CREATE INDEX ix1 ON inspect(projectID,IFMISSINGORNULL(isDeleted,flase),specialCircumstanceName, inspectionObjectID, spaceName, responsibleParties[0].documentID) WHERE type = 'defect' AND META().id NOT LIKE '_sync%';

SELECT meta().id, type, projectID, sinceEpoch, updatedAt, spaceCategoryName,
       spaceName, spaceNrList, unitName, unitNrList, objectName, objectNrList,
       detailName, detailNrList, proximusUnitName, proximusUnitNrList, proximusObjectName,
       proximusObjectNrList, errorCodeName, responsibleParties, remedyStates,
       inspectionObjectID, inspectionObjectTypeName, inspectionObjectNumber,
       specialCircumstanceName, specialCircumstanceAcronym, selfRegulationID, selfRegulationTypeName, photoIDs
FROM ispect AS D1 USE INDEX (ix1 USING GSI)
LET state = (SELECT RAW MAX([S1.inspectionNumber,S1])[1] FROM D1.remedyStates AS S1)[0]
WHERE type = 'defect' AND META().id NOT LIKE '_sync%'
      AND projectID = 'project_1503412693_b208a02b-ce84-438a-bdf9-3e3ab28edd08'
      AND IFMISSINGORNULL(isDeleted,flase) == false
      AND state.isRemedied = false
      AND (state.remedyStatus IS NOT VALUED OR state.remedyStatus[0].taggedAsRemediedBySubcontractor = false);


SELECT meta().id, type, projectID, sinceEpoch, updatedAt, spaceCategoryName,
       spaceName, spaceNrList, unitName, unitNrList, objectName, objectNrList,
       detailName, detailNrList, proximusUnitName, proximusUnitNrList, proximusObjectName,
       proximusObjectNrList, errorCodeName, responsibleParties, remedyStates,
       inspectionObjectID, inspectionObjectTypeName, inspectionObjectNumber,
       specialCircumstanceName, specialCircumstanceAcronym, selfRegulationID, selfRegulationTypeName, photoIDs
FROM ispect AS D1 USE INDEX (ix1 USING GSI)
LET state = (SELECT RAW MAX([S1.inspectionNumber,S1])[1] FROM D1.remedyStates AS S1)[0]
WHERE type = 'defect' AND META().id NOT LIKE '_sync%'
      AND projectID = 'project_1503412693_b208a02b-ce84-438a-bdf9-3e3ab28edd08'
      AND IFMISSINGORNULL(isDeleted,flase) == false
      AND state.isRemedied = false
      AND (state.remedyStatus IS NOT VALUED OR state.remedyStatus[0].taggedAsRemediedBySubcontractor = false)
      AND inspectionObjectID IN ['123123']
      AND responsibleParties[0].documentID IN ['123123']
      AND spaceName IN ['123123']
      AND specialCircumstanceName = 'Beställare'
      AND errorCodeName != '-'
      AND state.inspectionID IN ['123123']
      AND state.isRemedied = true
      AND state.remedyStatus[0].taggedAsRemediedBySubcontractor = true
      AND state.isRemedied = false
      AND (state.remedyStatus IS NOT VALUED OR state.remedyStatus[0].taggedAsRemediedBySubcontractor = false)

If you need further optimization, execute query in Query Workbench and Check PlanText Tab. If #itemsIn,#ItemsOut of Filter operator. If they are differed lot post the info.