Subquery counting related documents takes long time to evaluate

SQL++
1

Here is an example query I’m using

SELECT
    id,
    (
    SELECT COUNT(*) as count
        FROM default t USE KEYS (SELECT RAW meta().id from default)
        WHERE t.p_id=p.id
    )[0].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.

2

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

1 Like
3

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.

4

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 ...

1 Like