explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2BsUS : pg11

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 108.238 ↑ 1.0 1 1

Aggregate (cost=24,823.86..24,823.87 rows=1 width=8) (actual time=108.238..108.238 rows=1 loops=1)

  • Buffers: shared hit=15008, temp read=206 written=206
2. 0.000 108.225 ↓ 0.0 0 1

Nested Loop (cost=23,700.26..24,823.85 rows=3 width=16) (actual time=108.225..108.225 rows=0 loops=1)

  • Join Filter: (lesson_access.lesson_id = contributors.lesson_id)
  • Buffers: shared hit=15008, temp read=206 written=206
3. 0.000 108.225 ↓ 0.0 0 1

Nested Loop (cost=23,699.83..24,820.75 rows=1 width=96) (actual time=108.225..108.225 rows=0 loops=1)

  • Join Filter: (lesson_access.lesson_id = first_version.lesson_id)
  • Buffers: shared hit=15008, temp read=206 written=206
4. 0.001 108.225 ↓ 0.0 0 1

Nested Loop (cost=23,699.41..24,820.06 rows=1 width=80) (actual time=108.225..108.225 rows=0 loops=1)

  • Join Filter: (lesson_access.lesson_id = lesson_data.lesson_id)
  • Buffers: shared hit=15008, temp read=206 written=206
5. 0.000 108.224 ↓ 0.0 0 1

Nested Loop (cost=23,698.98..24,819.36 rows=1 width=72) (actual time=108.224..108.224 rows=0 loops=1)

  • Join Filter: (lesson_access.lesson_id = lesson_version.lesson_id)
  • Buffers: shared hit=15008, temp read=206 written=206
6. 0.041 108.224 ↓ 0.0 0 1

Nested Loop (cost=23,698.55..24,814.31 rows=1 width=52) (actual time=108.224..108.224 rows=0 loops=1)

  • Join Filter: (lesson_access.lesson_id = lesson.id)
  • Buffers: shared hit=15008, temp read=206 written=206
7. 3.630 107.832 ↓ 1.9 117 1

Hash Join (cost=23,698.13..24,732.15 rows=63 width=36) (actual time=104.103..107.832 rows=117 loops=1)

  • Hash Cond: (lesson_access.lesson_id = lesson_version_1.lesson_id)
  • Buffers: shared hit=14539, temp read=206 written=206
8. 0.199 0.253 ↓ 2.0 741 1

Bitmap Heap Scan on lesson_access (cost=36.19..1,069.24 rows=368 width=16) (actual time=0.066..0.253 rows=741 loops=1)

  • Recheck Cond: ((accessor_type = 'enterprise'::lesson_accessor_type) AND (accessor_id = '6c4e6676-ca7d-4b98-b320-87c063bfcee6'::uuid))
  • Heap Blocks: exact=92
  • Buffers: shared hit=111
9. 0.054 0.054 ↓ 2.0 741 1

Bitmap Index Scan on ix_lesson_access_accessor (cost=0.00..36.10 rows=368 width=0) (actual time=0.054..0.054 rows=741 loops=1)

  • Index Cond: ((accessor_type = 'enterprise'::lesson_accessor_type) AND (accessor_id = '6c4e6676-ca7d-4b98-b320-87c063bfcee6'::uuid))
  • Buffers: shared hit=19
10. 16.127 103.949 ↓ 4.2 82,351 1

Hash (cost=23,417.27..23,417.27 rows=19,574 width=20) (actual time=103.949..103.949 rows=82,351 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3329kB
  • Buffers: shared hit=14428, temp written=201
11. 52.160 87.822 ↓ 4.2 82,351 1

HashAggregate (cost=23,025.79..23,221.53 rows=19,574 width=20) (actual time=69.507..87.822 rows=82,351 loops=1)

  • Group Key: lesson_version_1.lesson_id
  • Buffers: shared hit=14428
12. 29.251 35.662 ↑ 1.0 130,919 1

Bitmap Heap Scan on lesson_version lesson_version_1 (cost=2,912.12..22,357.60 rows=133,638 width=20) (actual time=8.291..35.662 rows=130,919 loops=1)

  • Recheck Cond: (status = 'published'::lesson_status)
  • Heap Blocks: exact=13932
  • Buffers: shared hit=14428
13. 6.411 6.411 ↓ 1.1 145,830 1

Bitmap Index Scan on ix_lesson_version_status (cost=0.00..2,878.71 rows=133,638 width=0) (actual time=6.411..6.411 rows=145,830 loops=1)

  • Index Cond: (status = 'published'::lesson_status)
  • Buffers: shared hit=496
14. 0.351 0.351 ↓ 0.0 0 117

Index Scan using pk_lesson on lesson (cost=0.42..1.29 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=117)

  • Index Cond: (id = lesson_version_1.lesson_id)
  • Filter: ((deleted_at IS NULL) AND (archived_at IS NULL) AND (lesson_brand_id = ANY ('{2}'::integer[])) AND (enterprise_id = ANY ('{3f295fbc-ae0c-46a6-8681-5024a3e14d9b,e5463edc-7306-4062-a3da-d9f19b04047f}'::uuid[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=469
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lesson_version_lesson_id_version_id_status on lesson_version (cost=0.43..5.04 rows=1 width=20) (never executed)

  • Index Cond: ((lesson_id = lesson_version_1.lesson_id) AND (version_id = (max(lesson_version_1.version_id))))
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lesson_data_on_lesson_id_version_id_idx on lesson_data (cost=0.43..0.70 rows=1 width=20) (never executed)

  • Index Cond: ((lesson_id = lesson_version.lesson_id) AND (version_id = lesson_version.version_id))
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lesson_version_lesson_id_version_id_status on lesson_version first_version (cost=0.43..0.61 rows=6 width=16) (never executed)

  • Index Cond: ((lesson_id = lesson_data.lesson_id) AND (version_id = 1))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lesson_version_lesson_id_version_id_status on lesson_version contributors (cost=0.43..2.01 rows=87 width=16) (never executed)

  • Index Cond: (lesson_id = lesson_data.lesson_id)
  • Heap Fetches: 0