Query is slow for 135k documents, how to speed up

SQL++
4

You should use MERGE described Indexing for performance

CREATE INDEX ix1 ON metadata(parentId, locale, language, hasGeneratedChildren)
WHERE type = "testMetadataType" ;

PRE 6.50

    MERGE metadata AS m USING (SELECT s1.parentId, META(s2).id, META(s1).id AS pid
                               FROM metadata s1
                               JOIN FROM metadata s2
                               ON s1.locale = s2.locale AND s1.language = s2.language AND s1.parentId = s2.parentId
                               WHERE s1.type = "testMetadataType"
                                     AND s2.type = "testMetadataType"
                                     AND s1.hasGeneratedChildren = true ) AS s
    ON KEY s.id
    WHEN MATCHED THEN UPDATE m.parentDocumentId = s.pid;

6.50 https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/

MERGE metadata AS m USING (SELECT s1.parentId, s1.locale, s1.language, META(s1).id
                           FROM metadata s1
                           WHERE s1.type = "testMetadataType"
                                 AND s1.hasGeneratedChildren = true ) AS s
ON m.locale = s.locale AND m.language = s.language AND m.parentId = s.parentId AND m.type = "testMetadataType"
WHEN MATCHED THEN UPDATE m.parentDocumentId = s.id;
1 Like