SELECT COUNT(*) as count
FROM default t USE KEYS (SELECT RAW meta().id from default)
).count as children_count
FROM default p
My case and this solution are described at this topic on stackoverflow.
This query evaluates for around 4s on bucket containing around 200 documents, which is way too slow for me. I’ve tried to create indexes for id and p_id fields, but execution speed was not affected. How can I speed it up? I’m using version 4.5.1.
You are using primary index with many loops. Try this.
CREATE INDEX ixid ON default(id);
CREATE INDEX ixpid ON default(p_id);
SELECT RAW FIRST v FOR v IN ag WHEN v.p_id = p.id END FROM default p
LET ag = (SELECT p_id, COUNT(1) AS cnt FROM default WHERE p_id IS NOT NULL GROUP BY p_id)
WHERE p.id IS NOT NULL;
I think you should try Using CB 5.5 with ANSI JOIN