Estamos usando gemas ancestrales en nuestro proyecto de rieles. Hay alrededor de ~ 800 categorías en la tabla:
db => SELECT id, ancestry FROM product_categories LIMIT 10; id | ancestry -----+------------- 399 | 3 298 | 8/292/294 12 | 3/401/255 573 | 349/572 707 | 7/23/89/147 201 | 166/191 729 | 5/727 84 | 7/23 128 | 7/41/105 405 | 339 (10 rows) el campo de ancestry representa la "ruta" del registro. Lo que necesito es construir un mapa { category_id => [... all_subtree_ids ... ]}
Resolví esto usando subconsultas como esta:
SELECT id, ( SELECT array_agg(id) FROM product_categories WHERE (ancestry LIKE CONCAT(p.id, '/%') OR ancestry = CONCAT(p.ancestry, '/', p.id, '') OR ancestry = (p.id) :: TEXT) ) categories FROM product_categories p ORDER BY idlo que resulta en
1 | {17,470,32,29,15,836,845,837} 2 | {37,233,231,205,107,109,57,108,28,58, ...} PERO el problema es que esta consulta se ejecuta alrededor de 100 ms y me pregunto si hay una manera de optimizarla usando WITH recursive . Soy novato en CON, por lo que mis consultas solo cuelgan el postgres :(
** ========= UPD ========= ** Aceptó la respuesta de AlexM como la más rápida, pero si alguien está interesado, aquí hay una solución recursiva:
WITH RECURSIVE a AS (SELECT id, id as parent_id FROM product_categories UNION all SELECT pc.id, a.parent_id FROM product_categories pc, a WHERE regexp_replace(pc.ancestry, '^(\d{1,}/)*', '')::integer = a.id) SELECT parent_id, sort(array_agg(id)) as children FROM a WHERE id <> parent_id group by parent_id order by parent_id;Pruebe este enfoque, creo que debería ser mucho más rápido que las consultas anidadas:
WITH product_categories_flat AS ( SELECT id, unnest(string_to_array(ancestry, '/')) as parent FROM product_categories ) SELECT parent as id, array_agg(id) as children FROM product_categories_flat GROUP BY parentLo más probable es que una unión sea más rápida:
SELECT p1.id, p2.array_agg(id) FROM product_categories p JOIN product_categories p2 ON p2.ancestry LIKE CONCAT(p1.id, '/%') OR p2.ancestry = CONCAT(p1.ancestry, '/', p1.id) OR p2.ancestry = p1.id::text) GROUP BY p1.id ORDER BY p1.id; Pero para decir algo definitivo, tendría que mirar la salida EXPLAIN (ANALYZE, BUFFERS) .