#	exclusive	inclusive	rows x	rows	loops	node
1.	90.426	7,120.882	↓ 0.0	0	1	→ Delete on fact_paths fp (cost=13,477,981,128.48..14,987,141,349.41 rows=41,020 width=62) (actual time=7,120.882..7,120.882 rows=0 loops=1)
2.						CTE live_paths
3.	829.184	4,036.822	↑ 742,517.9	148,620	1	→ Recursive Union (cost=0.00..8,382,776,058.48 rows=110,353,003,200 width=64) (actual time=0.350..4,036.822 rows=148,620 loops=1)
4.	2,004.689	2,004.689	↓ 1.0	1,025,789	1	→ Seq Scan on factsets (cost=0.00..6,762.48 rows=1,003,200 width=64) (actual time=0.341..2,004.689 rows=1,025,789 loops=1)
5.	289.240	1,202.949	↑ 1,286,153.8	8,580	11	→ Nested Loop (cost=23.04..617,569,920.00 rows=11,035,200,000 width=64) (actual time=0.213..109.359 rows=8,580 loops=11)
6.	21.989	21.989	↑ 742.5	13,511	11	→ WorkTable Scan on live_paths live_paths_1 (cost=0.00..200,640.00 rows=10,032,000 width=64) (actual time=0.001..1.999 rows=13,511 loops=11)
7.	445.860	891.720	↑ 1,100.0	1	148,620	→ HashAggregate (cost=23.04..34.04 rows=1,100 width=64) (actual time=0.006..0.006 rows=1 loops=148,620) Group Key: ((jsonb_each(live_paths_1.value)).key), ((jsonb_each(live_paths_1.value)).value)
8.	0.000	445.860	↑ 1,100.0	1	148,620	→ Append (cost=0.01..17.54 rows=1,100 width=64) (actual time=0.003..0.003 rows=1 loops=148,620)
9.	445.860	445.860	↑ 100.0	1	148,620	→ Result (cost=0.01..0.52 rows=100 width=64) (actual time=0.002..0.003 rows=1 loops=148,620) One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'object'::text)
10.	0.000	0.000	↓ 0.0	0	148,620	→ Result (cost=0.01..5.03 rows=1,000 width=64) (actual time=0.000..0.000 rows=0 loops=148,620) One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'array'::text)
11.	2,492.178	7,030.456	↓ 1.7	70,764	1	→ Hash Anti Join (cost=5,095,205,070.00..6,604,365,290.93 rows=41,020 width=62) (actual time=4,607.041..7,030.456 rows=70,764 loops=1) Hash Cond: (fp.path = live_paths.path)
12.	28.958	28.958	↑ 1.0	80,085	1	→ Seq Scan on fact_paths fp (cost=0.00..3,750.40 rows=82,040 width=106) (actual time=0.038..28.958 rows=80,085 loops=1)
13.	378.210	4,509.320	↑ 742,517.9	148,620	1	→ Hash (cost=2,207,060,064.00..2,207,060,064.00 rows=110,353,003,200 width=88) (actual time=4,509.320..4,509.320 rows=148,620 loops=1) Buckets: 1048576 Batches: 262144 Memory Usage: 8197kB
14.	4,131.110	4,131.110	↑ 742,517.9	148,620	1	→ CTE Scan on live_paths (cost=0.00..2,207,060,064.00 rows=110,353,003,200 width=88) (actual time=0.359..4,131.110 rows=148,620 loops=1)

                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on fact_paths fp  (cost=13477981128.48..14987141349.41 rows=41020 width=62) (actual time=7120.882..7120.882 rows=0 loops=1)
   CTE live_paths
     ->  Recursive Union  (cost=0.00..8382776058.48 rows=110353003200 width=64) (actual time=0.350..4036.822 rows=148620 loops=1)
           ->  Seq Scan on factsets  (cost=0.00..6762.48 rows=1003200 width=64) (actual time=0.341..2004.689 rows=1025789 loops=1)
           ->  Nested Loop  (cost=23.04..617569920.00 rows=11035200000 width=64) (actual time=0.213..109.359 rows=8580 loops=11)
                 ->  WorkTable Scan on live_paths live_paths_1  (cost=0.00..200640.00 rows=10032000 width=64) (actual time=0.001..1.999 rows=13511 loops=11)
                 ->  HashAggregate  (cost=23.04..34.04 rows=1100 width=64) (actual time=0.006..0.006 rows=1 loops=148620)
                       Group Key: ((jsonb_each(live_paths_1.value)).key), ((jsonb_each(live_paths_1.value)).value)
                       ->  Append  (cost=0.01..17.54 rows=1100 width=64) (actual time=0.003..0.003 rows=1 loops=148620)
                             ->  Result  (cost=0.01..0.52 rows=100 width=64) (actual time=0.002..0.003 rows=1 loops=148620)
                                   One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'object'::text)
                             ->  Result  (cost=0.01..5.03 rows=1000 width=64) (actual time=0.000..0.000 rows=0 loops=148620)
                                   One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'array'::text)
   ->  Hash Anti Join  (cost=5095205070.00..6604365290.93 rows=41020 width=62) (actual time=4607.041..7030.456 rows=70764 loops=1)
         Hash Cond: (fp.path = live_paths.path)
         ->  Seq Scan on fact_paths fp  (cost=0.00..3750.40 rows=82040 width=106) (actual time=0.038..28.958 rows=80085 loops=1)
         ->  Hash  (cost=2207060064.00..2207060064.00 rows=110353003200 width=88) (actual time=4509.320..4509.320 rows=148620 loops=1)
               Buckets: 1048576  Batches: 262144  Memory Usage: 8197kB
               ->  CTE Scan on live_paths  (cost=0.00..2207060064.00 rows=110353003200 width=88) (actual time=0.359..4131.110 rows=148620 loops=1)
 Planning time: 0.646 ms
 Execution time: 7178.876 ms
(21 rows)

Per node type stats

node type	count	sum of times	% of query
Append	1	0.000 ms	0.0 %
CTE Scan	1	4,131.110 ms	58.0 %
Delete	1	90.426 ms	1.3 %
Hash	1	378.210 ms	5.3 %
Hash Anti Join	1	2,492.178 ms	35.0 %
HashAggregate	1	445.860 ms	6.3 %
Nested Loop	1	289.240 ms	4.1 %
Recursive Union	1	829.184 ms	11.6 %
Result	2	445.860 ms	6.3 %
Seq Scan	2	2,033.647 ms	28.6 %
WorkTable Scan on live_paths live_paths_1	1	21.989 ms	0.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
fact_paths	2	119.384 ms	1.7 %
Delete	1	90.426 ms	75.7 %
Seq Scan	1	28.958 ms	24.3 %
factsets	1	2,004.689 ms	28.2 %
Seq Scan	1	2,004.689 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cCF : new pg 10 query w/o temp tables

Color mode:

Visible columns:

Per node type stats

Per table stats