couchbase/couchbase-lite-ios

Random query behavior with same index created in different moments

matteosist opened this issue · 3 comments

matteosist commented

Describe the bug
Hi, i've an application with about 500k documents and i have many complex queries, so queries optimization with properly indexes is mandatory.

I don't know if i'm creating indexes in the right way but in this moment with this indexes i've sufficient performances for my use case. I want to take a single query as example but this issue is present to all queries randomly.

I have the following query:

QueryBuilder
    .selectDistinct(
        SelectResult.expression(Expression.property("SEQ_FASE").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.phaseId.rawValue),
        SelectResult.expression(Expression.property("DESCR").from("EATFASIB")).as(DataActivity.CodingKeys.phaseDescription.rawValue),
        SelectResult.expression(Expression.property("NUM_COM").from("EADCMANAZAGOP")).as(DataActivity.CodingKeys.productionOrderCode.rawValue),
        SelectResult.expression(Expression.property("CODART").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.articleCode.rawValue),
        SelectResult.expression(Expression.property("DESART").from("EAMANAGRZAGOP")).as(DataActivity.CodingKeys.articleDescription.rawValue),
        SelectResult.expression(Expression.value("")).as(DataActivity.CodingKeys.phaseStateId.rawValue),
        SelectResult.expression(Expression.property("SEQ_FASE").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.sequence.rawValue),
        SelectResult.expression(Expression.value("")).as(DataActivity.CodingKeys.phaseSpecs.rawValue),
        SelectResult.expression(Expression.boolean(true)).as(DataActivity.CodingKeys.takingEnabled.rawValue),
        SelectResult.expression(Expression.boolean(true)).as(DataActivity.CodingKeys.editingEnabled.rawValue),
        SelectResult.expression(Expression.boolean(true)).as(DataActivity.CodingKeys.partialProductionEnabled.rawValue),
        SelectResult.expression(Expression.property("QT_DPROD").from("EADCMANAZAGOP")).as(DataActivity.CodingKeys.totalQuantity.rawValue),
        SelectResult.expression(
            Function.sum(Expression.property("QTAPROD").from("EAPOPESE"))
        ).as(DataActivity.CodingKeys.completedQuantity.rawValue),
        SelectResult.expression(Expression.boolean(true)).as(DataActivity.CodingKeys.highlightsEnabled.rawValue),
        SelectResult.expression(Expression.value(0)).as(DataActivity.CodingKeys.weight.rawValue),
        SelectResult.expression(Expression.value("")).as(DataActivity.CodingKeys.position.rawValue),
        SelectResult.expression(Expression.value("")).as(DataActivity.CodingKeys.activityNote.rawValue),
        SelectResult.expression(Expression.value("")).as(DataActivity.CodingKeys.activeUserCode.rawValue),
        SelectResult.expression(Expression.property("REPARTO").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.areaCode.rawValue),
        SelectResult.expression(Expression.boolean(true)).as(DataActivity.CodingKeys.interactionEnabled.rawValue),
        SelectResult.expression(Expression.property("CONS_PREV").from("EADCMANAZAGOP")).as(DataActivity.CodingKeys.deliveryDate.rawValue),
        SelectResult.expression(Expression.boolean(false)).as(DataActivity.CodingKeys.activeOnPhase.rawValue),
        SelectResult.expression(Expression.property("ULTIMO").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.lastActivity.rawValue),
        SelectResult.expression(Expression.property("FASE").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.phaseCode.rawValue),
        SelectResult.expression(Expression.property("FFASE").from("EADCMCILZAGOP")).as(DataActivity.CodingKeys.phaseFamilyCode.rawValue)
    )
    .from(DataSource.database(database).as("EADCMCILZAGOP"))
    .join(
        Join.innerJoin(DataSource.database(database).as("EATFASIB"))
            .on(
                Expression.property("FAMFASI").from("EATFASIB").equalTo(Expression.property("FFASE").from("EADCMCILZAGOP"))
                    .and(Expression.property("FASE").from("EATFASIB").equalTo(Expression.property("FASE").from("EADCMCILZAGOP")))
                    .and(Expression.property("type").from("EATFASIB").equalTo(Expression.value("EATFASIB")))
        ),
        Join.innerJoin(DataSource.database(database).as("EAMANAGRZAGOP"))
            .on(
                Expression.property("CODART").from("EAMANAGRZAGOP").equalTo(Expression.property("CODART").from("EADCMCILZAGOP"))
                    .and(Expression.property("type").from("EAMANAGRZAGOP").equalTo(Expression.value("EAMANAGRZAGOP")))
        ),
        Join.innerJoin(DataSource.database(database).as("EADCMANAZAGOP"))
            .on(
                Expression.property("NUM_COM").from("EADCMANAZAGOP").equalTo(Expression.property("NUM_COM").from("EADCMCILZAGOP"))
                    .and(Expression.property("type").from("EADCMANAZAGOP").equalTo(Expression.value("EADCMANAZAGOP")))
        ),
        Join.leftJoin(DataSource.database(database).as("EAPOPESE"))
            .on(
                Expression.property("NUMCOMM").from("EAPOPESE").equalTo(Expression.property("NUM_COM").from("EADCMCILZAGOP"))
                    .and(Expression.property("CI_SFASE").from("EAPOPESE").equalTo(Expression.property("SEQ_FASE").from("EADCMCILZAGOP")))
                    .and(Expression.property("type").from("EAPOPESE").equalTo(Expression.value("EAPOPESE")))
        )
    )
    .where(whereClause(
        Expression.property("type").from("EADCMCILZAGOP").equalTo(Expression.string("EADCMCILZAGOP")))
    )
    .groupBy(
        Expression.property("NUM_COM").from("EADCMANAZAGOP"),
        Expression.property("SEQ_FASE").from("EADCMCILZAGOP"),
        Expression.property("DESCR").from("EATFASIB"),
        Expression.property("CODART").from("EADCMCILZAGOP"),
        Expression.property("DESART").from("EAMANAGRZAGOP"),
        Expression.property("QT_DPROD").from("EADCMANAZAGOP"),
        Expression.property("FASE").from("EADCMCILZAGOP"),
        Expression.property("CONS_PREV").from("EADCMANAZAGOP"),
        Expression.property("ULTIMO").from("EADCMCILZAGOP")
    )
    .having(
        Function.sum(Expression.property("QTAPROD").from("EAPOPESE")).isNullOrMissing()
            .or(
                Expression.property("QT_DPROD").from("EADCMANAZAGOP")
                    .greaterThan(Function.sum(Expression.property("QTAPROD").from("EAPOPESE")))
            )
    )

Without indexes (i mean only with single index on "type" field with name "TypeIndex") this query takes from 30 to 90 seconds to return result (based on device performance).

So i create this index with name "ActivitiesIndexWithWhere":

        IndexBuilder.valueIndex(items:
            ValueIndexItem.expression(Expression.property("type")),
            ValueIndexItem.expression(Expression.property("REPARTO")),
            ValueIndexItem.expression(Expression.property("QT_DPROD")),
            ValueIndexItem.expression(Expression.property("QTAPROD")),
            ValueIndexItem.expression(Expression.property("NUMCOMM")),
            ValueIndexItem.expression(Expression.property("NUM_COM")),
            ValueIndexItem.expression(Expression.property("CODART")),
            ValueIndexItem.expression(Expression.property("FAMFASI")),
            ValueIndexItem.expression(Expression.property("FFASE")),
            ValueIndexItem.expression(Expression.property("FASE")),
            ValueIndexItem.expression(Expression.property("CI_SFASE")),
            ValueIndexItem.expression(Expression.property("SEQ_FASE"))
        )

Of course i have more other indexes. If are needed i can provide to you.

With this index the query takes from 0.05 to 3 second to return result (acceptable).

I know that maybe i can improve the performance but can be enough for me.
But randomly, after create the index, query takes more than 30 seconds to return result without any reason. If i delete the app and then i reinstall again, all works perfectly. No changes happens on dataset or queries or indexes.

So i try to debug for many times until i tried to run "explain()" function on query before run the query between two consecutive re-installations and i get two different results.

In the first case i get this explanation (and in this case i get the result in 0.1 sec):

SELECT DISTINCT fl_result(fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AS \"phaseId\", fl_result(fl_value(\"EATFASIB\".body, \'DESCR\')) AS \"phaseDescription\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\')) AS \"productionOrderCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'CODART\')) AS \"articleCode\", fl_result(fl_value(\"EAMANAGRZAGOP\".body, \'DESART\')) AS \"articleDescription\", fl_result(\'\') AS \"phaseStateId\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AS \"sequence\", fl_result(\'\') AS \"phaseSpecs\", fl_result(fl_bool(1)) AS \"takingEnabled\", fl_result(fl_bool(1)) AS \"editingEnabled\", fl_result(fl_bool(1)) AS \"partialProductionEnabled\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\')) AS \"totalQuantity\", fl_result(sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\'))) AS \"completedQuantity\", fl_result(fl_bool(1)) AS \"highlightsEnabled\", fl_result(0) AS \"weight\", fl_result(\'\') AS \"position\", fl_result(\'\') AS \"activityNote\", fl_result(\'\') AS \"activeUserCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'REPARTO\')) AS \"areaCode\", fl_result(fl_bool(1)) AS \"interactionEnabled\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'CONS_PREV\')) AS \"deliveryDate\", fl_result(fl_bool(0)) AS \"activeOnPhase\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'ULTIMO\')) AS \"lastActivity\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'FASE\')) AS \"phaseCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'FFASE\')) AS \"phaseFamilyCode\" FROM kv_default AS \"EADCMCILZAGOP\" INNER JOIN kv_default AS \"EATFASIB\" ON ((fl_value(\"EATFASIB\".body, \'FAMFASI\') = fl_value(\"EADCMCILZAGOP\".body, \'FFASE\') AND fl_value(\"EATFASIB\".body, \'FASE\') = fl_value(\"EADCMCILZAGOP\".body, \'FASE\')) AND fl_value(\"EATFASIB\".body, \'type\') = \'EATFASIB\') AND (\"EATFASIB\".flags & 1 = 0) INNER JOIN kv_default AS \"EAMANAGRZAGOP\" ON (fl_value(\"EAMANAGRZAGOP\".body, \'CODART\') = fl_value(\"EADCMCILZAGOP\".body, \'CODART\') AND fl_value(\"EAMANAGRZAGOP\".body, \'type\') = \'EAMANAGRZAGOP\') AND (\"EAMANAGRZAGOP\".flags & 1 = 0) INNER JOIN kv_default AS \"EADCMANAZAGOP\" ON (fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\') = fl_value(\"EADCMCILZAGOP\".body, \'NUM_COM\') AND fl_value(\"EADCMANAZAGOP\".body, \'type\') = \'EADCMANAZAGOP\') AND (\"EADCMANAZAGOP\".flags & 1 = 0) LEFT OUTER JOIN kv_default AS \"EAPOPESE\" ON ((fl_value(\"EAPOPESE\".body, \'NUMCOMM\') = fl_value(\"EADCMCILZAGOP\".body, \'NUM_COM\') AND fl_value(\"EAPOPESE\".body, \'CI_SFASE\') = fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AND fl_value(\"EAPOPESE\".body, \'type\') = \'EAPOPESE\') AND (\"EAPOPESE\".flags & 1 = 0) WHERE (fl_value(\"EADCMCILZAGOP\".body, \'type\') = \'EADCMCILZAGOP\' AND fl_value(\"EADCMCILZAGOP\".body, \'REPARTO\') IN (\'00700\')) AND (\"EADCMCILZAGOP\".flags & 1 = 0) GROUP BY fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\'), fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\'), fl_value(\"EATFASIB\".body, \'DESCR\'), fl_value(\"EADCMCILZAGOP\".body, \'CODART\'), fl_value(\"EAMANAGRZAGOP\".body, \'DESART\'), fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\'), fl_value(\"EADCMCILZAGOP\".body, \'FASE\'), fl_value(\"EADCMANAZAGOP\".body, \'CONS_PREV\'), fl_value(\"EADCMCILZAGOP\".body, \'ULTIMO\') HAVING (sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\')) = fl_null() OR sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\')) IS NULL) OR fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\') > sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\'))

16|0|0| SEARCH TABLE kv_default AS EADCMANAZAGOP USING INDEX ComponentsIndexWithWhere (<expr>=?)
53|0|0| SEARCH TABLE kv_default AS EAMANAGRZAGOP USING INDEX ArticlesIndexWithWhere (<expr>=? AND <expr>=?)
32|0|0| SEARCH TABLE kv_default AS EADCMCILZAGOP USING INDEX ActivitiesFiltersIndexWithWhere (<expr>=? AND <expr>=?)
24|0|0| SEARCH TABLE kv_default AS EATFASIB USING INDEX TypeIndex (<expr>=?)
64|0|0| SEARCH TABLE kv_default AS EAPOPESE USING INDEX TypeIndex (<expr>=?)
90|0|0| USE TEMP B-TREE FOR GROUP BY
220|0|0| USE TEMP B-TREE FOR DISTINCT

{\"WHAT\":[[\"AS\",[\".EADCMCILZAGOP.SEQ_FASE\"],\"phaseId\"],[\"AS\",[\".EATFASIB.DESCR\"],\"phaseDescription\"],[\"AS\",[\".EADCMANAZAGOP.NUM_COM\"],\"productionOrderCode\"],[\"AS\",[\".EADCMCILZAGOP.CODART\"],\"articleCode\"],[\"AS\",[\".EAMANAGRZAGOP.DESART\"],\"articleDescription\"],[\"AS\",\"\",\"phaseStateId\"],[\"AS\",[\".EADCMCILZAGOP.SEQ_FASE\"],\"sequence\"],[\"AS\",\"\",\"phaseSpecs\"],[\"AS\",true,\"takingEnabled\"],[\"AS\",true,\"editingEnabled\"],[\"AS\",true,\"partialProductionEnabled\"],[\"AS\",[\".EADCMANAZAGOP.QT_DPROD\"],\"totalQuantity\"],[\"AS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],\"completedQuantity\"],[\"AS\",true,\"highlightsEnabled\"],[\"AS\",0,\"weight\"],[\"AS\",\"\",\"position\"],[\"AS\",\"\",\"activityNote\"],[\"AS\",\"\",\"activeUserCode\"],[\"AS\",[\".EADCMCILZAGOP.REPARTO\"],\"areaCode\"],[\"AS\",true,\"interactionEnabled\"],[\"AS\",[\".EADCMANAZAGOP.CONS_PREV\"],\"deliveryDate\"],[\"AS\",false,\"activeOnPhase\"],[\"AS\",[\".EADCMCILZAGOP.ULTIMO\"],\"lastActivity\"],[\"AS\",[\".EADCMCILZAGOP.FASE\"],\"phaseCode\"],[\"AS\",[\".EADCMCILZAGOP.FFASE\"],\"phaseFamilyCode\"]],\"GROUP_BY\":[[\".EADCMANAZAGOP.NUM_COM\"],[\".EADCMCILZAGOP.SEQ_FASE\"],[\".EATFASIB.DESCR\"],[\".EADCMCILZAGOP.CODART\"],[\".EAMANAGRZAGOP.DESART\"],[\".EADCMANAZAGOP.QT_DPROD\"],[\".EADCMCILZAGOP.FASE\"],[\".EADCMANAZAGOP.CONS_PREV\"],[\".EADCMCILZAGOP.ULTIMO\"]],\"FROM\":[{\"AS\":\"EADCMCILZAGOP\"},{\"ON\":[\"AND\",[\"AND\",[\"=\",[\".EATFASIB.FAMFASI\"],[\".EADCMCILZAGOP.FFASE\"]],[\"=\",[\".EATFASIB.FASE\"],[\".EADCMCILZAGOP.FASE\"]]],[\"=\",[\".EATFASIB.type\"],\"EATFASIB\"]],\"AS\":\"EATFASIB\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"=\",[\".EAMANAGRZAGOP.CODART\"],[\".EADCMCILZAGOP.CODART\"]],[\"=\",[\".EAMANAGRZAGOP.type\"],\"EAMANAGRZAGOP\"]],\"AS\":\"EAMANAGRZAGOP\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"=\",[\".EADCMANAZAGOP.NUM_COM\"],[\".EADCMCILZAGOP.NUM_COM\"]],[\"=\",[\".EADCMANAZAGOP.type\"],\"EADCMANAZAGOP\"]],\"AS\":\"EADCMANAZAGOP\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"AND\",[\"=\",[\".EAPOPESE.NUMCOMM\"],[\".EADCMCILZAGOP.NUM_COM\"]],[\"=\",[\".EAPOPESE.CI_SFASE\"],[\".EADCMCILZAGOP.SEQ_FASE\"]]],[\"=\",[\".EAPOPESE.type\"],\"EAPOPESE\"]],\"AS\":\"EAPOPESE\",\"JOIN\":\"LEFT OUTER\"}],\"WHERE\":[\"AND\",[\"=\",[\".EADCMCILZAGOP.type\"],\"EADCMCILZAGOP\"],[\"IN\",[\".EADCMCILZAGOP.REPARTO\"],[\"[]\",\"00700\"]]],\"DISTINCT\":true,\"HAVING\":[\"OR\",[\"OR\",[\"IS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],null],[\"IS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],[\"MISSING\"]]],[\">\",[\".EADCMANAZAGOP.QT_DPROD\"],[\"SUM()\",[\".EAPOPESE.QTAPROD\"]]]]}

In the second case i get this explanation (and query takes 30 seconds):

SELECT DISTINCT fl_result(fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AS \"phaseId\", fl_result(fl_value(\"EATFASIB\".body, \'DESCR\')) AS \"phaseDescription\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\')) AS \"productionOrderCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'CODART\')) AS \"articleCode\", fl_result(fl_value(\"EAMANAGRZAGOP\".body, \'DESART\')) AS \"articleDescription\", fl_result(\'\') AS \"phaseStateId\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AS \"sequence\", fl_result(\'\') AS \"phaseSpecs\", fl_result(fl_bool(1)) AS \"takingEnabled\", fl_result(fl_bool(1)) AS \"editingEnabled\", fl_result(fl_bool(1)) AS \"partialProductionEnabled\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\')) AS \"totalQuantity\", fl_result(sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\'))) AS \"completedQuantity\", fl_result(fl_bool(1)) AS \"highlightsEnabled\", fl_result(0) AS \"weight\", fl_result(\'\') AS \"position\", fl_result(\'\') AS \"activityNote\", fl_result(\'\') AS \"activeUserCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'REPARTO\')) AS \"areaCode\", fl_result(fl_bool(1)) AS \"interactionEnabled\", fl_result(fl_value(\"EADCMANAZAGOP\".body, \'CONS_PREV\')) AS \"deliveryDate\", fl_result(fl_bool(0)) AS \"activeOnPhase\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'ULTIMO\')) AS \"lastActivity\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'FASE\')) AS \"phaseCode\", fl_result(fl_value(\"EADCMCILZAGOP\".body, \'FFASE\')) AS \"phaseFamilyCode\" FROM kv_default AS \"EADCMCILZAGOP\" INNER JOIN kv_default AS \"EATFASIB\" ON ((fl_value(\"EATFASIB\".body, \'FAMFASI\') = fl_value(\"EADCMCILZAGOP\".body, \'FFASE\') AND fl_value(\"EATFASIB\".body, \'FASE\') = fl_value(\"EADCMCILZAGOP\".body, \'FASE\')) AND fl_value(\"EATFASIB\".body, \'type\') = \'EATFASIB\') AND (\"EATFASIB\".flags & 1 = 0) INNER JOIN kv_default AS \"EAMANAGRZAGOP\" ON (fl_value(\"EAMANAGRZAGOP\".body, \'CODART\') = fl_value(\"EADCMCILZAGOP\".body, \'CODART\') AND fl_value(\"EAMANAGRZAGOP\".body, \'type\') = \'EAMANAGRZAGOP\') AND (\"EAMANAGRZAGOP\".flags & 1 = 0) INNER JOIN kv_default AS \"EADCMANAZAGOP\" ON (fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\') = fl_value(\"EADCMCILZAGOP\".body, \'NUM_COM\') AND fl_value(\"EADCMANAZAGOP\".body, \'type\') = \'EADCMANAZAGOP\') AND (\"EADCMANAZAGOP\".flags & 1 = 0) LEFT OUTER JOIN kv_default AS \"EAPOPESE\" ON ((fl_value(\"EAPOPESE\".body, \'NUMCOMM\') = fl_value(\"EADCMCILZAGOP\".body, \'NUM_COM\') AND fl_value(\"EAPOPESE\".body, \'CI_SFASE\') = fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\')) AND fl_value(\"EAPOPESE\".body, \'type\') = \'EAPOPESE\') AND (\"EAPOPESE\".flags & 1 = 0) WHERE (fl_value(\"EADCMCILZAGOP\".body, \'type\') = \'EADCMCILZAGOP\' AND fl_value(\"EADCMCILZAGOP\".body, \'REPARTO\') IN (\'00700\')) AND (\"EADCMCILZAGOP\".flags & 1 = 0) GROUP BY fl_value(\"EADCMANAZAGOP\".body, \'NUM_COM\'), fl_value(\"EADCMCILZAGOP\".body, \'SEQ_FASE\'), fl_value(\"EATFASIB\".body, \'DESCR\'), fl_value(\"EADCMCILZAGOP\".body, \'CODART\'), fl_value(\"EAMANAGRZAGOP\".body, \'DESART\'), fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\'), fl_value(\"EADCMCILZAGOP\".body, \'FASE\'), fl_value(\"EADCMANAZAGOP\".body, \'CONS_PREV\'), fl_value(\"EADCMCILZAGOP\".body, \'ULTIMO\') HAVING (sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\')) = fl_null() OR sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\')) IS NULL) OR fl_value(\"EADCMANAZAGOP\".body, \'QT_DPROD\') > sum(fl_value(\"EAPOPESE\".body, \'QTAPROD\'))

16|0|0| SEARCH TABLE kv_default AS EADCMANAZAGOP USING INDEX ComponentsIndexWithWhere (<expr>=?)
24|0|0| SEARCH TABLE kv_default AS EAMANAGRZAGOP USING INDEX TypeIndex (<expr>=?)
32|0|0| SEARCH TABLE kv_default AS EADCMCILZAGOP USING INDEX ComponentsIndexWithWhere (<expr>=? AND <expr>=? AND <expr>=?)
49|0|0| SEARCH TABLE kv_default AS EATFASIB USING INDEX TypeIndex (<expr>=?)
67|0|0| SEARCH TABLE kv_default AS EAPOPESE USING INDEX TypeIndex (<expr>=?)
91|0|0| USE TEMP B-TREE FOR GROUP BY
222|0|0| USE TEMP B-TREE FOR DISTINCT

{\"WHAT\":[[\"AS\",[\".EADCMCILZAGOP.SEQ_FASE\"],\"phaseId\"],[\"AS\",[\".EATFASIB.DESCR\"],\"phaseDescription\"],[\"AS\",[\".EADCMANAZAGOP.NUM_COM\"],\"productionOrderCode\"],[\"AS\",[\".EADCMCILZAGOP.CODART\"],\"articleCode\"],[\"AS\",[\".EAMANAGRZAGOP.DESART\"],\"articleDescription\"],[\"AS\",\"\",\"phaseStateId\"],[\"AS\",[\".EADCMCILZAGOP.SEQ_FASE\"],\"sequence\"],[\"AS\",\"\",\"phaseSpecs\"],[\"AS\",true,\"takingEnabled\"],[\"AS\",true,\"editingEnabled\"],[\"AS\",true,\"partialProductionEnabled\"],[\"AS\",[\".EADCMANAZAGOP.QT_DPROD\"],\"totalQuantity\"],[\"AS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],\"completedQuantity\"],[\"AS\",true,\"highlightsEnabled\"],[\"AS\",0,\"weight\"],[\"AS\",\"\",\"position\"],[\"AS\",\"\",\"activityNote\"],[\"AS\",\"\",\"activeUserCode\"],[\"AS\",[\".EADCMCILZAGOP.REPARTO\"],\"areaCode\"],[\"AS\",true,\"interactionEnabled\"],[\"AS\",[\".EADCMANAZAGOP.CONS_PREV\"],\"deliveryDate\"],[\"AS\",false,\"activeOnPhase\"],[\"AS\",[\".EADCMCILZAGOP.ULTIMO\"],\"lastActivity\"],[\"AS\",[\".EADCMCILZAGOP.FASE\"],\"phaseCode\"],[\"AS\",[\".EADCMCILZAGOP.FFASE\"],\"phaseFamilyCode\"]],\"GROUP_BY\":[[\".EADCMANAZAGOP.NUM_COM\"],[\".EADCMCILZAGOP.SEQ_FASE\"],[\".EATFASIB.DESCR\"],[\".EADCMCILZAGOP.CODART\"],[\".EAMANAGRZAGOP.DESART\"],[\".EADCMANAZAGOP.QT_DPROD\"],[\".EADCMCILZAGOP.FASE\"],[\".EADCMANAZAGOP.CONS_PREV\"],[\".EADCMCILZAGOP.ULTIMO\"]],\"FROM\":[{\"AS\":\"EADCMCILZAGOP\"},{\"ON\":[\"AND\",[\"AND\",[\"=\",[\".EATFASIB.FAMFASI\"],[\".EADCMCILZAGOP.FFASE\"]],[\"=\",[\".EATFASIB.FASE\"],[\".EADCMCILZAGOP.FASE\"]]],[\"=\",[\".EATFASIB.type\"],\"EATFASIB\"]],\"AS\":\"EATFASIB\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"=\",[\".EAMANAGRZAGOP.CODART\"],[\".EADCMCILZAGOP.CODART\"]],[\"=\",[\".EAMANAGRZAGOP.type\"],\"EAMANAGRZAGOP\"]],\"AS\":\"EAMANAGRZAGOP\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"=\",[\".EADCMANAZAGOP.NUM_COM\"],[\".EADCMCILZAGOP.NUM_COM\"]],[\"=\",[\".EADCMANAZAGOP.type\"],\"EADCMANAZAGOP\"]],\"AS\":\"EADCMANAZAGOP\",\"JOIN\":\"INNER\"},{\"ON\":[\"AND\",[\"AND\",[\"=\",[\".EAPOPESE.NUMCOMM\"],[\".EADCMCILZAGOP.NUM_COM\"]],[\"=\",[\".EAPOPESE.CI_SFASE\"],[\".EADCMCILZAGOP.SEQ_FASE\"]]],[\"=\",[\".EAPOPESE.type\"],\"EAPOPESE\"]],\"AS\":\"EAPOPESE\",\"JOIN\":\"LEFT OUTER\"}],\"WHERE\":[\"AND\",[\"=\",[\".EADCMCILZAGOP.type\"],\"EADCMCILZAGOP\"],[\"IN\",[\".EADCMCILZAGOP.REPARTO\"],[\"[]\",\"00700\"]]],\"DISTINCT\":true,\"HAVING\":[\"OR\",[\"OR\",[\"IS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],null],[\"IS\",[\"SUM()\",[\".EAPOPESE.QTAPROD\"]],[\"MISSING\"]]],[\">\",[\".EADCMANAZAGOP.QT_DPROD\"],[\"SUM()\",[\".EAPOPESE.QTAPROD\"]]]]}

What i'm doing wrong? Why i get different results? And why same query, with same dataset, use different indexes?
I saw that in the query explanation there are more indexes used and not only "ActivitiesIndexWithWhere". So maybe i need to create indexes in another way, but which way? I'm not able to found exhaustive documentation on this topic.

Platform (please complete the following information):

  • Device: [e.g. iPad, Simulator]
  • OS: [iOS 13+]
  • Couchbase Version [e.g. CouchbaseLiteSwift 2.7.1]

Many thanks.

jayahariv commented

Is it possible to share a unit test / project with the issue reproduced? If not, please share the below details too, I will try to reproduce it from my side.

Can you share the below index creation code too?

  1. ComponentsIndexWithWhere
  2. TypeIndex
  3. ArticlesIndexWithWhere

I have a simple unit test, which includes five indexes, and couple of joins which is always returning the same explain for the query. Also how often are you seeing the change?

jayahariv commented

@matteosist If you are still facing the issue, could you please check and provide the requested information?

jayahariv commented

Closing for now, in case you are still facing the issue feel free to reopen the issue with requested details.