explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1ny4 : lateral with less orphans

Settings
# exclusive inclusive rows x rows loops node
1. 14.531 5,908.983 ↓ 0.0 0 1

Delete on fact_paths fp (cost=13,441,707,815.83..14,946,383,115.92 rows=3,390 width=62) (actual time=5,908.983..5,908.983 rows=0 loops=1)

2.          

CTE live_paths

3. 817.483 3,749.428 ↑ 898,321.0 122,513 1

Recursive Union (cost=0.00..8,360,215,917.57 rows=110,056,000,500 width=64) (actual time=0.661..3,749.428 rows=122,513 loops=1)

4. 2,055.971 2,055.971 ↓ 1.0 1,032,338 1

Seq Scan on factsets (cost=0.00..7,902.58 rows=1,000,500 width=64) (actual time=0.653..2,055.971 rows=1,032,338 loops=1)

5. 243.455 875.974 ↑ 1,681,255.7 6,546 11

Nested Loop (cost=23.04..615,907,800.00 rows=11,005,500,000 width=64) (actual time=0.060..79.634 rows=6,546 loops=11)

6. 19.954 19.954 ↑ 898.3 11,138 11

WorkTable Scan on live_paths live_paths_1 (cost=0.00..200,100.00 rows=10,005,000 width=64) (actual time=0.001..1.814 rows=11,138 loops=11)

7. 367.539 612.565 ↑ 1,100.0 1 122,513

HashAggregate (cost=23.04..34.04 rows=1,100 width=64) (actual time=0.005..0.005 rows=1 loops=122,513)

  • Group Key: ((jsonb_each(live_paths_1.value)).key), ((jsonb_each(live_paths_1.value)).value)
8. 0.000 245.026 ↑ 1,100.0 1 122,513

Append (cost=0.01..17.54 rows=1,100 width=64) (actual time=0.002..0.002 rows=1 loops=122,513)

9. 245.026 245.026 ↑ 100.0 1 122,513

Result (cost=0.01..0.52 rows=100 width=64) (actual time=0.002..0.002 rows=1 loops=122,513)

  • One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'object'::text)
10. 0.000 0.000 ↓ 0.0 0 122,513

Result (cost=0.01..5.03 rows=1,000 width=64) (actual time=0.000..0.000 rows=0 loops=122,513)

  • One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'array'::text)
11. 1,903.050 5,894.452 ↓ 1.6 5,344 1

Hash Anti Join (cost=5,081,491,898.25..6,586,167,198.35 rows=3,390 width=62) (actual time=4,017.976..5,894.452 rows=5,344 loops=1)

  • Hash Cond: (fp.path = live_paths.path)
12. 4.915 4.915 ↑ 1.1 6,161 1

Seq Scan on fact_paths fp (cost=0.00..293.80 rows=6,780 width=96) (actual time=0.052..4.915 rows=6,161 loops=1)

13. 169.783 3,986.487 ↑ 898,321.0 122,513 1

Hash (cost=2,201,120,010.00..2,201,120,010.00 rows=110,056,000,500 width=88) (actual time=3,986.486..3,986.487 rows=122,513 loops=1)

  • Buckets: 1048576 Batches: 262144 Memory Usage: 8193kB
14. 3,816.704 3,816.704 ↑ 898,321.0 122,513 1

CTE Scan on live_paths (cost=0.00..2,201,120,010.00 rows=110,056,000,500 width=88) (actual time=0.677..3,816.704 rows=122,513 loops=1)