explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9y4e : pg12

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 2,523.512 ↑ 1.0 1 1

Aggregate (cost=1,623,930.46..1,623,930.47 rows=1 width=8) (actual time=2,523.512..2,523.512 rows=1 loops=1)

  • Buffers: shared hit=139765 read=59
  • I/O Timings: read=0.312
2. 6.682 2,523.479 ↓ 0.0 0 1

Nested Loop (cost=14,991.38..16,859.67 rows=642,828,315 width=16) (actual time=2,523.479..2,523.479 rows=0 loops=1)

  • Join Filter: (lesson.id = lesson_access.lesson_id)
  • Buffers: shared hit=139762 read=59
  • I/O Timings: read=0.312
3. 1.850 2,229.463 ↓ 8,451.0 8,451 1

Nested Loop (cost=14,977.42..16,841.69 rows=1 width=96) (actual time=88.186..2,229.463 rows=8,451 loops=1)

  • Join Filter: (lesson.id = lesson_data.lesson_id)
  • Buffers: shared hit=55257 read=54
  • I/O Timings: read=0.283
4. 1.522 2,210.711 ↓ 8,451.0 8,451 1

Nested Loop (cost=14,977.00..16,839.65 rows=1 width=88) (actual time=88.175..2,210.711 rows=8,451 loops=1)

  • Join Filter: (lesson.id = contributors.lesson_id)
  • Buffers: shared hit=21507
5. 0.233 2,206.009 ↓ 265.0 265 1

Nested Loop (cost=14,976.57..16,584.44 rows=1 width=72) (actual time=88.166..2,206.009 rows=265 loops=1)

  • Join Filter: (lesson.id = first_version.lesson_id)
  • Buffers: shared hit=19227
6. 1.079 2,204.981 ↓ 265.0 265 1

Nested Loop (cost=14,976.14..16,582.58 rows=1 width=56) (actual time=88.141..2,204.981 rows=265 loops=1)

  • Join Filter: (lesson.id = lesson_version.lesson_id)
  • Buffers: shared hit=18167
7. 672.024 2,201.517 ↓ 265.0 265 1

Nested Loop (cost=14,971.32..16,445.07 rows=1 width=36) (actual time=88.109..2,201.517 rows=265 loops=1)

  • Join Filter: (lesson.id = lesson_version_1.lesson_id)
  • Rows Removed by Join Filter: 10994690
  • Buffers: shared hit=17107
8. 16.273 19.145 ↓ 266.0 266 1

Bitmap Heap Scan on lesson (cost=12.78..1,480.03 rows=1 width=16) (actual time=3.742..19.145 rows=266 loops=1)

  • Recheck Cond: (archived_at IS NULL)
  • Filter: ((deleted_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: 91899
  • Heap Blocks: exact=2733
  • Buffers: shared hit=2988
9. 2.872 2.872 ↓ 158.4 92,165 1

Bitmap Index Scan on ix_archived_at (cost=0.00..12.78 rows=582 width=0) (actual time=2.872..2.872 rows=92,165 loops=1)

  • Index Cond: (archived_at IS NULL)
  • Buffers: shared hit=255
10. 1,474.996 1,510.348 ↓ 206.7 41,334 266

HashAggregate (cost=14,958.54..14,960.54 rows=200 width=20) (actual time=0.267..5.678 rows=41,334 loops=266)

  • Group Key: lesson_version_1.lesson_id
  • Buffers: shared hit=14119
11. 28.660 35.352 ↓ 15.4 130,919 1

Bitmap Heap Scan on lesson_version lesson_version_1 (cost=162.23..14,916.08 rows=8,491 width=20) (actual time=8.569..35.352 rows=130,919 loops=1)

  • Recheck Cond: (status = 'published'::lesson_status)
  • Heap Blocks: exact=13758
  • Buffers: shared hit=14119
12. 6.692 6.692 ↓ 15.4 130,919 1

Bitmap Index Scan on ix_lesson_version_status (cost=0.00..160.11 rows=8,491 width=0) (actual time=6.692..6.692 rows=130,919 loops=1)

  • Index Cond: (status = 'published'::lesson_status)
  • Buffers: shared hit=361
13. 0.795 2.385 ↑ 42.0 1 265

Bitmap Heap Scan on lesson_version (cost=4.82..136.99 rows=42 width=20) (actual time=0.009..0.009 rows=1 loops=265)

  • Recheck Cond: ((lesson_id = lesson_version_1.lesson_id) AND (version_id = (max(lesson_version_1.version_id))))
  • Heap Blocks: exact=265
  • Buffers: shared hit=1060
14. 1.590 1.590 ↑ 42.0 1 265

Bitmap Index Scan on lesson_version_lesson_id_version_id_status (cost=0.00..4.81 rows=42 width=0) (actual time=0.006..0.006 rows=1 loops=265)

  • Index Cond: ((lesson_id = lesson_version_1.lesson_id) AND (version_id = (max(lesson_version_1.version_id))))
  • Buffers: shared hit=795
15. 0.795 0.795 ↑ 42.0 1 265

Index Only Scan using lesson_version_lesson_id_version_id_status on lesson_version first_version (cost=0.43..1.33 rows=42 width=16) (actual time=0.003..0.003 rows=1 loops=265)

  • Index Cond: ((lesson_id = lesson_version.lesson_id) AND (version_id = 1))
  • Heap Fetches: 265
  • Buffers: shared hit=1060
16. 3.180 3.180 ↑ 265.3 32 265

Index Only Scan using lesson_version_lesson_id_version_id_status on lesson_version contributors (cost=0.43..149.08 rows=8,491 width=16) (actual time=0.002..0.012 rows=32 loops=265)

  • Index Cond: (lesson_id = lesson_version.lesson_id)
  • Heap Fetches: 8451
  • Buffers: shared hit=2280
17. 16.902 16.902 ↑ 42.0 1 8,451

Index Only Scan using lesson_data_on_lesson_id_version_id_idx on lesson_data (cost=0.43..1.51 rows=42 width=20) (actual time=0.002..0.002 rows=1 loops=8,451)

  • Index Cond: ((lesson_id = lesson_version.lesson_id) AND (version_id = lesson_version.version_id))
  • Heap Fetches: 8451
  • Buffers: shared hit=33750 read=54
  • I/O Timings: read=0.283
18. 8.451 287.334 ↓ 0.0 0 8,451

Bitmap Heap Scan on lesson_access (cost=13.96..17.98 rows=1 width=16) (actual time=0.034..0.034 rows=0 loops=8,451)

  • Recheck Cond: ((accessor_type = 'enterprise'::lesson_accessor_type) AND (accessor_id = '6c4e6676-ca7d-4b98-b320-87c063bfcee6'::uuid) AND (lesson_id = lesson_version.lesson_id))
  • Buffers: shared hit=84505 read=5
  • I/O Timings: read=0.028
19. 8.451 278.883 ↓ 0.0 0 8,451

BitmapAnd (cost=13.96..13.96 rows=1 width=0) (actual time=0.033..0.033 rows=0 loops=8,451)

  • Buffers: shared hit=84505 read=5
  • I/O Timings: read=0.028
20. 253.530 253.530 ↓ 123.5 741 8,451

Bitmap Index Scan on ix_lesson_access_accessor (cost=0.00..4.48 rows=6 width=0) (actual time=0.030..0.030 rows=741 loops=8,451)

  • Index Cond: ((accessor_type = 'enterprise'::lesson_accessor_type) AND (accessor_id = '6c4e6676-ca7d-4b98-b320-87c063bfcee6'::uuid))
  • Buffers: shared hit=59152 read=5
  • I/O Timings: read=0.028
21. 16.902 16.902 ↑ 587.0 2 8,451

Bitmap Index Scan on ix_lesson_access_lesson_id (cost=0.00..9.23 rows=1,174 width=0) (actual time=0.002..0.002 rows=2 loops=8,451)

  • Index Cond: (lesson_id = lesson_version.lesson_id)" Buffers: shared hit=25353