explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eCFa

Settings
# exclusive inclusive rows x rows loops node
1. 131.208 9,335.415 ↓ 0.0 0 1

Delete on fact_paths fp (cost=4,103,046,028.31..4,562,393,731.97 rows=13,877 width=62) (actual time=9,335.415..9,335.415 rows=0 loops=1)

2.          

CTE live_paths

3. 405.816 1,808.437 ↑ 873,282.5 38,469 1

Recursive Union (cost=0.00..2,551,933,957.81 rows=33,594,305,400 width=64) (actual time=0.994..1,808.437 rows=38,469 loops=1)

4. 956.901 956.901 ↓ 1.0 316,815 1

Seq Scan on factsets (cost=0.00..2,395.81 rows=305,400 width=64) (actual time=0.987..956.901 rows=316,815 loops=1)

5. 93.163 445.720 ↑ 1,624,468.1 2,068 11

Nested Loop (cost=23.04..188,004,240.00 rows=3,359,400,000 width=64) (actual time=0.096..40.520 rows=2,068 loops=11)

6. 6.336 6.336 ↑ 873.3 3,497 11

WorkTable Scan on live_paths live_paths_1 (cost=0.00..61,080.00 rows=3,054,000 width=64) (actual time=0.000..0.576 rows=3,497 loops=11)

7. 192.345 346.221 ↑ 1,100.0 1 38,469

HashAggregate (cost=23.04..34.04 rows=1,100 width=64) (actual time=0.008..0.009 rows=1 loops=38,469)

  • Group Key: ((jsonb_each(live_paths_1.value)).key), ((jsonb_each(live_paths_1.value)).value)
8. 38.469 153.876 ↑ 1,100.0 1 38,469

Append (cost=0.01..17.54 rows=1,100 width=64) (actual time=0.003..0.004 rows=1 loops=38,469)

9. 115.407 115.407 ↑ 100.0 1 38,469

Result (cost=0.01..0.52 rows=100 width=64) (actual time=0.003..0.003 rows=1 loops=38,469)

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

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

  • One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'array'::text)
11. 7,161.140 9,204.207 ↓ 2.0 27,134 1

Hash Anti Join (cost=1,551,112,070.50..2,010,459,774.16 rows=13,877 width=62) (actual time=3,925.798..9,204.207 rows=27,134 loops=1)

  • Hash Cond: (fp.path = live_paths.path)
12. 45.683 45.683 ↑ 1.0 27,754 1

Seq Scan on fact_paths fp (cost=0.00..1,306.54 rows=27,754 width=105) (actual time=0.028..45.683 rows=27,754 loops=1)

13. 143.566 1,997.384 ↑ 873,282.5 38,469 1

Hash (cost=671,886,108.00..671,886,108.00 rows=33,594,305,400 width=88) (actual time=1,997.384..1,997.384 rows=38,469 loops=1)

  • Buckets: 32768 Batches: 524288 Memory Usage: 696kB
14. 1,853.818 1,853.818 ↑ 873,282.5 38,469 1

CTE Scan on live_paths (cost=0.00..671,886,108.00 rows=33,594,305,400 width=88) (actual time=1.002..1,853.818 rows=38,469 loops=1)