explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Peiw

Settings
# exclusive inclusive rows x rows loops node
1. 191.777 7,385.579 ↓ 0.0 0 1

Delete on fact_paths fp (cost=13,477,981,128.48..14,987,125,526.31 rows=40,240 width=62) (actual time=7,385.579..7,385.579 rows=0 loops=1)

2.          

CTE live_paths

3. 829.030 3,989.930 ↑ 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.342..3,989.930 rows=148,620 loops=1)

4. 1,963.022 1,963.022 ↓ 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.336..1,963.022 rows=1,025,789 loops=1)

5. 306.158 1,197.878 ↑ 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.210..108.898 rows=8,580 loops=11)

  • -> WorkTable Scan on live_paths live_paths_1 (cost=0.00..200640.00 rows=10032000 width=64) (actual time=0.001..2.220 rows=13511 lo
6. 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)
7. 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)

8. 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)
9. 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)
10. 2,711.292 7,193.802 ↓ 1.8 70,764 1

Hash Anti Join (cost=5,095,205,070.00..6,604,349,467.83 rows=40,240 width=62) (actual time=4,548.791..7,193.802 rows=70,764 loops=1)

  • Hash Cond: (fp.path = live_paths.path)
11. 35.535 35.535 ↑ 1.0 80,085 1

Seq Scan on fact_paths fp (cost=0.00..3,834.80 rows=80,480 width=106) (actual time=0.035..35.535 rows=80,085 loops=1)

12. 366.662 4,446.975 ↑ 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,446.974..4,446.975 rows=148,620 loops=1)

  • Buckets: 1048576 Batches: 262144 Memory Usage: 8197kB
13. 4,080.313 4,080.313 ↑ 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.350..4,080.313 rows=148,620 loops=1)