Here is an example query I’m using
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
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
Your code works way faster. How can I modify it to query some other fields as well? I tried to do
SELECT RAW FIRST v FOR v IN ag WHEN v.p_id = p.id END, p.* FROM default p ... but it seems wrong.
REMOVE RAW, RAW only for single projection to remove top level object
SELECT (FIRST v FOR v IN ag WHEN v.p_id = p.id END).*, p.* FROM default p ...