Random query behavior with same index created in different moments

[matteosist]

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]

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]

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

[jayahariv]

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