Hi,
I've been playing around with hierarchical queries a bit and one thing
I wanted to do is build a query that gives the ultimate parent for a
given child.
The two queries below seem to be a good a shortlist as any.
I'm no query-plan guru, but these seem to be largely identical aside
from one uses "filter IS NULL" and the other uses "top-N heapsort".
Would there be a reason to prefer one over the other (or perhaps
there's an altogether more efficient way of doing this query ?!?).
My gut-instinct says the sort version ?
=> explain analyze with recursive cte(cmenu_id,depth,cmenu_parent) as (
SELECT cmenu_id,1 as depth,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,cte.depth-1,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte order by depth LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=166.59..166.59 rows=1 width=68) (actual
time=0.132..0.132 rows=1 loops=1)
CTE cte
-> Recursive Union (cost=0.15..165.31 rows=51 width=68) (actual
time=0.023..0.070 rows=4 loops=1)
-> Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=68) (actual time=0.022..0.022 rows=1
loops=1)
Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
Filter: cmenu_active
-> Hash Join (cost=0.33..15.61 rows=5 width=68) (actual
time=0.009..0.010 rows=1 loops=4)
Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
-> Seq Scan on cms_menu c (cost=0.00..14.40
rows=220 width=64) (actual time=0.002..0.004 rows=12 loops=3)
Filter: cmenu_active
-> Hash (cost=0.20..0.20 rows=10 width=36) (actual
time=0.002..0.002 rows=1 loops=4)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.001 rows=1
loops=4)
-> Sort (cost=1.28..1.40 rows=51 width=68) (actual
time=0.131..0.131 rows=1 loops=1)
Sort Key: cte.depth
Sort Method: top-N heapsort Memory: 25kB
-> CTE Scan on cte (cost=0.00..1.02 rows=51 width=68)
(actual time=0.024..0.073 rows=4 loops=1)
Planning time: 0.221 ms
Execution time: 0.163 ms
(19 rows)
=>explain analyze with recursive cte(cmenu_id,cmenu_parent) as (
SELECT cmenu_id,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte where cmenu_parent IS NULL LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=165.19..166.21 rows=1 width=64) (actual
time=0.069..0.069 rows=1 loops=1)
CTE cte
-> Recursive Union (cost=0.15..165.19 rows=51 width=64) (actual
time=0.020..0.064 rows=4 loops=1)
-> Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=64) (actual time=0.019..0.020 rows=1
loops=1)
Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
Filter: cmenu_active
-> Hash Join (cost=0.33..15.60 rows=5 width=64) (actual
time=0.011..0.012 rows=1 loops=3)
Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
-> Seq Scan on cms_menu c (cost=0.00..14.40
rows=220 width=64) (actual time=0.003..0.005 rows=9 loops=3)
Filter: cmenu_active
-> Hash (cost=0.20..0.20 rows=10 width=32) (actual
time=0.002..0.002 rows=1 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=32) (actual time=0.001..0.001 rows=1
loops=3)
-> CTE Scan on cte (cost=0.00..1.02 rows=1 width=64) (actual
time=0.068..0.068 rows=1 loops=1)
Filter: (cmenu_parent IS NULL)
Rows Removed by Filter: 3
Planning time: 0.302 ms
Execution time: 0.105 ms
(18 rows)
