Find the size of subtree in a PostgreSQL table which represents adjacency list relations

subtree_size.sql

-- For example we have such table:
-- Table
-- id  parent_id             
-- ------------- 
-- 0   NULL      
-- 1   0         
-- 2   0         
-- 3   1         
-- 4   1
--
-- And it is necessary to determine the subtree size for each node
-- id  size
-- ----------
-- 0   5
-- 1   3
-- 2   1
-- 3   1
-- 4   1

-- It could be done with the query below
-- use BFS search to build path for each node
WITH RECURSIVE bfs AS (
    SELECT id, value, array[id] AS path
    FROM Keyword
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT C.id, C.value, P.path || C.id
    FROM Keyword C JOIN bfs P ON P.id = C.parent_id
)

SELECT *, (SELECT COUNT(*) FROM bfs P WHERE P.path @> D.path) FROM bfs D ORDER BY id;