explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OVPpi

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 110,823.213 ↓ 1.3 252 1

Limit (cost=32,634,312.37..32,634,314.37 rows=200 width=8) (actual time=110,823.131..110,823.213 rows=252 loops=1)

  • Buffers: shared hit=1042178 read=100819 dirtied=5014
  • I/O Timings: read=98720.992
2.          

CTE base_and_descendants

3. 4,161.195 109,092.064 ↑ 140.2 1,138,217 1

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

  • Buffers: shared hit=1042178 read=100819 dirtied=5014
  • I/O Timings: read=98720.992
4. 101,146.081 101,146.081 ↑ 1.0 1,137,965 1

Index Scan using index_namespaces_on_type_partial on public.namespaces (cost=0.43..104,880.54 rows=1,148,346 width=326) (actual time=0.053..101,146.081 rows=1,137,965 loops=1)

  • Index Cond: ((namespaces.type)::text = 'Group'::text)
  • Buffers: shared hit=399649 read=99420 dirtied=4483
  • I/O Timings: read=97706.594
5. 311.433 3,784.788 ↑ 151.8 104,406 3

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

  • Buffers: shared hit=642529 read=1399 dirtied=531
  • I/O Timings: read=1014.398
6. 2,192.082 2,192.082 ↑ 5.3 218,518 3

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

  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 719
  • Buffers: shared hit=642529 read=1399 dirtied=531
  • I/O Timings: read=1014.398
7. 150.840 1,281.273 ↑ 25.7 447,423 3

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

8. 688.698 1,130.433 ↑ 30.3 379,403 3

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

  • Sort Key: base_and_descendants_1.id
  • Sort Method: quicksort Memory: 26kB
9. 441.735 441.735 ↑ 30.3 379,406 3

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

10. 0.412 110,823.186 ↓ 1.3 252 1

HashAggregate (cost=3,856,767.90..3,856,769.90 rows=200 width=8) (actual time=110,823.130..110,823.186 rows=252 loops=1)

  • Group Key: base_and_descendants.id
  • Buffers: shared hit=1042178 read=100819 dirtied=5014
  • I/O Timings: read=98720.992
11. 110,822.774 110,822.774 ↑ 211,098.4 252 1

CTE Scan on base_and_descendants (cost=0.00..3,590,783.91 rows=53,196,799 width=8) (actual time=107,179.339..110,822.774 rows=252 loops=1)

  • Filter: (base_and_descendants.visibility_level > base_and_descendants.strictest_parent_level)
  • Rows Removed by Filter: 1137965
  • Buffers: shared hit=1042178 read=100819 dirtied=5014
  • I/O Timings: read=98720.992