Slow select by index

SQL++
1

I try to run query

Blockquote SELECT ARRAY_AGG(t.task_id) as data FROM task_bot AS t
LEFT JOIN task_bot AS tb ON KEYS (“BotTask::” || TO_STRING(t.task_id) || “__888999”)
WHERE t._type=“Task”
AND t.status = 0
AND t.network=“instagram”
AND t.type= “follower”
AND tb IS MISSING
In this query I want to select all documents ID _type=“Task” which not contains in other

I use index
CREATE INDEX adv_type_network_task_id_type_status ON task_bot(type,network,task_id) WHERE ((_type = ‘Task’) and (status = 0))

Total items 1,693,268
But the query is running elapsed: 800.5ms execution: 734.9ms

Why why so slow ?!
Where am I going wrong? help me please

2

Every document need to look if “BotTask” is present via fetch if not there project it.
It all depends on how many LEFT side document qualified. Check profile and see

Also subquery it self is AGG see if you can avoid ARRAY_AGG

SELECT (
         SELECT RAW t.task_id FROM task_bot AS t
         LEFT JOIN task_bot AS tb ON KEYS (“BotTask::” || TO_STRING(t.task_id) || “__888999”)
         WHERE t._type="Task"
                           AND t.status = 0
                           AND t.network="instagram"
                          AND t.type= "follower"
                         AND tb IS MISSING) AS data;