#	exclusive	inclusive	rows x	rows	loops	node
1.	62480.876	62480.876	↓ 16686.9	133495	1	→ CTE Scan on rec_sub_tree (cost=1465505.41..1472461.19 rows=8 width=132) (actual time=0.067..62480.876 rows=133495 loops=1) Filter: ((parent_id IS NULL) AND (forest_id = 'Forest A'::text))
2.						CTE rec_sub_tree
3.	11860.699	53736.585	↓ 5.3	1645992	1	→ Recursive Union (cost=0.00..1465505.41 rows=309146 width=150) (actual time=0.048..53736.585 rows=1645992 loops=1)
4.	975.796	975.796	↑ 1.0	247316	1	→ Seq Scan on tree_data_1 td (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.034..975.796 rows=247316 loops=1)
5.	21385.644	40900.090	↓ 32.3	199811	7	→ Hash Join (cost=13097.90..145331.63 rows=6183 width=150) (actual time=2087.065..5842.870 rows=199811 loops=7) Hash Cond: ((rec.forest_id = td.forest_id) AND (rec.parent_id = td.node_id))
6.	6410.698	6410.698	↑ 5.3	235142	7	→ WorkTable Scan on rec_sub_tree rec (cost=0.00..49463.20 rows=1236580 width=132) (actual time=0.017..915.814 rows=235142 loops=7) Filter: (NOT cycle)
7.	6994.673	13103.748	↑ 1.0	247316	7	→ Hash (cost=6006.16..6006.16 rows=247316 width=82) (actual time=1871.964..1871.964 rows=247316 loops=7)
8.	6109.075	6109.075	↑ 1.0	247316	7	→ Seq Scan on tree_data_1 td (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.017..872.725 rows=247316 loops=7)

 CTE Scan on rec_sub_tree  (cost=1465505.41..1472461.19 rows=8 width=132) (actual time=0.067..62480.876 rows=133495 loops=1)
   Filter: ((parent_id IS NULL) AND (forest_id = 'Forest A'::text))
   CTE rec_sub_tree
     ->  Recursive Union  (cost=0.00..1465505.41 rows=309146 width=150) (actual time=0.048..53736.585 rows=1645992 loops=1)
           ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.034..975.796 rows=247316 loops=1)
           ->  Hash Join  (cost=13097.90..145331.63 rows=6183 width=150) (actual time=2087.065..5842.870 rows=199811 loops=7)
                 Hash Cond: ((rec.forest_id = td.forest_id) AND (rec.parent_id = td.node_id))
                 ->  WorkTable Scan on rec_sub_tree rec  (cost=0.00..49463.20 rows=1236580 width=132) (actual time=0.017..915.814 rows=235142 loops=7)
                       Filter: (NOT cycle)
                 ->  Hash  (cost=6006.16..6006.16 rows=247316 width=82) (actual time=1871.964..1871.964 rows=247316 loops=7)
                       ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.017..872.725 rows=247316 loops=7)
 Total runtime: 62978.883 ms
(12 rows)

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	62480.876 ms	100.0 %
Hash	1	6994.673 ms	11.2 %
Hash Join	1	21385.644 ms	34.2 %
Recursive Union	1	11860.699 ms	19.0 %
Seq Scan	2	7084.871 ms	11.3 %
WorkTable Scan on rec_sub_tree rec	1	6410.698 ms	10.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
tree_data_1	2	7084.871 ms	11.3 %
Seq Scan	2	7084.871 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: NTD6

Colorize:

Columns visibility:

Per node type stats

Per table stats