explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cGT8

Settings
# exclusive inclusive rows x rows loops node
1. 1,763.169 149,627.045 ↓ 0.0 0 1

ModifyTable on public.namespaces (cost=33,858,970.27..40,337,646.23 rows=53,196,799 width=372) (actual time=149,627.045..149,627.045 rows=0 loops=1)

  • Buffers: shared hit=5151408 read=135252 dirtied=14080 written=31164
  • I/O Timings: read=113108.745 write=5011.110
2.          

CTE namespaces_levels

3. 120,249.005 120,249.005 ↑ 211,098.4 252 1

CTE Scan on base_and_descendants base_and_descendants_1 (cost=28,777,544.46..32,368,328.37 rows=53,196,799 width=8) (actual time=116,497.893..120,249.005 rows=252 loops=1)

  • Filter: (base_and_descendants_1.visibility_level > base_and_descendants_1.strictest_parent_level)
  • Rows Removed by Filter: 1137965
  • Buffers: shared hit=1041888 read=101135 dirtied=5133 written=24458
  • I/O Timings: read=101739.718 write=2652.280
4.          

CTE base_and_descendants

5. 5,714.513 116,713.646 ↑ 140.2 1,138,217 1

Recursive Union (cost=0.43..28,777,544.46 rows=159,590,396 width=326) (actual time=3.195..116,713.646 rows=1,138,217 loops=1)

  • Buffers: shared hit=1041888 read=101135 dirtied=5133 written=24458
  • I/O Timings: read=101739.718 write=2652.280
6. 107,150.832 107,150.832 ↑ 1.0 1,137,965 1

Index Scan using index_namespaces_on_type_partial on public.namespaces namespaces_1 (cost=0.43..104,880.54 rows=1,148,346 width=326) (actual time=3.167..107,150.832 rows=1,137,965 loops=1)

  • Index Cond: ((namespaces_1.type)::text = 'Group'::text)
  • Buffers: shared hit=399316 read=99754 dirtied=4613 written=24458
  • I/O Timings: read=100715.694 write=2652.280
7. 316.356 3,848.301 ↑ 151.8 104,406 3

Merge Join (cost=1,890,286.67..2,548,085.60 rows=15,844,205 width=326) (actual time=477.077..1,282.767 rows=104,406 loops=3)

  • Buffers: shared hit=642572 read=1381 dirtied=520
  • I/O Timings: read=1024.024
8. 2,258.589 2,258.589 ↑ 5.3 218,518 3

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.43..388,556.77 rows=1,148,346 width=322) (actual time=1.081..752.863 rows=218,518 loops=3)

  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 719
  • Buffers: shared hit=642567 read=1381 dirtied=520
  • I/O Timings: read=1024.024
9. 154.164 1,273.356 ↑ 25.7 447,423 3

Materialize (cost=1,890,286.24..1,947,703.54 rows=11,483,460 width=8) (actual time=287.979..424.452 rows=447,423 loops=3)

  • Buffers: shared hit=5
10. 748.245 1,119.192 ↑ 30.3 379,403 3

Sort (cost=1,890,286.24..1,918,994.89 rows=11,483,460 width=8) (actual time=286.705..373.064 rows=379,403 loops=3)

  • Sort Key: base_and_descendants.id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=5
11. 370.947 370.947 ↑ 30.3 379,406 3

WorkTable Scan on base_and_descendants (cost=0.00..229,669.20 rows=11,483,460 width=8) (actual time=0.039..123.649 rows=379,406 loops=3)

12. 1,948.780 147,863.876 ↑ 211,098.4 252 1

Hash Join (cost=1,490,641.90..7,969,317.86 rows=53,196,799 width=372) (actual time=142,304.698..147,863.876 rows=252 loops=1)

  • Hash Cond: (namespaces_levels.id = namespaces.id)
  • Buffers: shared hit=5138170 read=133794 dirtied=12707 written=31164
  • I/O Timings: read=111412.250 write=5011.110
13. 120,249.416 120,249.416 ↑ 211,098.4 252 1

CTE Scan on namespaces_levels (cost=0.00..1,063,935.98 rows=53,196,799 width=40) (actual time=116,497.945..120,249.416 rows=252 loops=1)

  • Buffers: shared hit=1041888 read=101135 dirtied=5133 written=24458
  • I/O Timings: read=101739.718 write=2652.280
14. 4,757.962 25,665.680 ↑ 1.0 5,943,450 1

Hash (cost=390,019.56..390,019.56 rows=5,969,467 width=324) (actual time=25,665.680..25,665.680 rows=5,943,450 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 28422kB
  • Buffers: shared hit=4096282 read=32659 dirtied=7574 written=6706
  • I/O Timings: read=9672.532 write=2358.830
15. 20,907.718 20,907.718 ↑ 1.0 5,943,450 1

Index Scan using namespaces_pkey on public.namespaces (cost=0.43..390,019.56 rows=5,969,467 width=324) (actual time=2.917..20,907.718 rows=5,943,450 loops=1)

  • Buffers: shared hit=4096282 read=32659 dirtied=7574 written=6706
  • I/O Timings: read=9672.532 write=2358.830