N1QL index when LIMIT and OR condition exist

Team, urgent we are in production and w ehave one issue on index.

SELECT * FROM optima WHERE type = ‘processinstance’ and (assignee.roleId==‘44a906ad-0889-44b7-b890-588f27632b61’ or processQueueId in [4009]) limit 10

created 2 index for above query
CREATE INDEX idx_type_assigneerole ON optima(type,(assignee.roleId)) WHERE (type = “processinstance”) WITH { “nodes”:[ “10.131.108.13:8091” ] }
CREATE INDEX idx_type_processQueueId ON optima(type,processQueueId) WHERE (type = “processinstance”) WITH { “defer_build”:true }

Above both the index scanned when i’m not using LIMIT on query and its fast response, the same when i use LIMIT it hangs can you please guide how to create Index for following QUERY
SELECT * FROM optima WHERE type = ‘processinstance’ and (assignee.roleId==‘44a906ad-0889-44b7-b890-588f27632b61’ or processQueueId in [4009] )and ANY var IN processInstHistory SATISFIES var.createDate >='1970-01-01T00:00:00.000Z’END LIMIT 100 OFFSET 0

I am not sure why it hangs. You can try latest CB version.

CREATE INDEX  ix1  ON  optima ( assignee.roleId) WHERE type  =  "processinstance";
CREATE INDEX  ix2  ON  optima ( processQueueId) WHERE type  =  "processinstance";

SELECT o.*
FROM optima AS o
WHERE   o.type  =  "processinstance" 
                     AND (o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61"  OR  o.processQueueId IN [4009] )
LIMIT 10;

SELECT o.*
FROM optima AS o
WHERE   o.type  =  "processinstance" 
                     AND o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61" 
UNION 
SELECT o.*
FROM optima AS o
WHERE   o.type  =  "processinstance" 
                     AND  o.processQueueId IN [4009] 
LIMIT 10;

You can also try this if LIMIT is high

SELECT  *
FROM optima  USE KEYS ( SELECT RAW META(o).id
                      FROM optima AS o
                                   WHERE   o.type  =  "processinstance" 
                                                    AND o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61" 
                UNION 
                        SELECT RAW META(o).id
                      FROM optima AS o
                            WHERE   o.type  =  "processinstance" 
                                     AND  o.processQueueId IN [4009] 
                         LIMIT 10)];

For other query use following indexes and modify query as above

 CREATE INDEX  ix3  ON  optima ( assignee.roleId, DISTINCT ARRAY c.createDate FOR c IN processInstHistory END) WHERE type  =  "processinstance";
    CREATE INDEX  ix3  ON  optima ( processQueueId, DISTINCT ARRAY c.createDate FOR c IN processInstHistory END) WHERE type  =  "processinstance";


Thanks a lot. we are changing query to use UNION query works .