explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 20Um

Settings
# exclusive inclusive rows x rows loops node
1. 13.986 8,243.071 ↑ 10.0 25,749 1

Sort (cost=564,116.36..564,760.23 rows=257,550 width=44) (actual time=8,241.119..8,243.071 rows=25,749 loops=1)

  • Sort Key: parents.id
  • Sort Method: quicksort Memory: 2780kB
  • Functions: 14
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.832 ms, Inlining 7.040 ms, Optimization 94.457 ms, Emission 57.746 ms, Total 163.074 ms
2.          

CTE parents

3. 25.978 8,213.696 ↑ 10.0 25,749 1

Recursive Union (cost=0.43..527,894.70 rows=257,550 width=44) (actual time=153.439..8,213.696 rows=25,749 loops=1)

4. 153.475 154.122 ↑ 12.8 200 1

Nested Loop (cost=0.43..17,834.25 rows=2,550 width=44) (actual time=153.435..154.122 rows=200 loops=1)

5. 0.047 0.047 ↑ 12.8 200 1

Seq Scan on refs200 r (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.026..0.047 rows=200 loops=1)

6. 0.600 0.600 ↑ 1.0 1 200

Index Scan using delta_pkey on delta (cost=0.43..6.98 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=200)

  • Index Cond: (id = r.id)
7. 2,227.530 8,033.596 ↑ 1,821.4 14 1,811

Merge Join (cost=2,376.80..50,490.95 rows=25,500 width=44) (actual time=1.403..4.436 rows=14 loops=1,811)

  • Merge Cond: (d.id = p.parentdeltaid)
8. 5,787.956 5,787.956 ↑ 61.2 20,620 1,811

Index Scan using delta_pkey on delta d (cost=0.43..44,577.41 rows=1,261,865 width=44) (actual time=0.006..3.196 rows=20,620 loops=1,811)

9. 9.055 18.110 ↑ 1,821.4 14 1,811

Sort (cost=2,376.37..2,440.12 rows=25,500 width=4) (actual time=0.009..0.010 rows=14 loops=1,811)

  • Sort Key: p.parentdeltaid
  • Sort Method: quicksort Memory: 25kB
10. 9.055 9.055 ↑ 1,821.4 14 1,811

WorkTable Scan on parents p (cost=0.00..510.00 rows=25,500 width=4) (actual time=0.004..0.005 rows=14 loops=1,811)

11. 8,229.085 8,229.085 ↑ 10.0 25,749 1

CTE Scan on parents (cost=0.00..5,151.00 rows=257,550 width=44) (actual time=153.444..8,229.085 rows=25,749 loops=1)