explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hd9a

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 61,754.305 ↑ 1.0 31 1

Limit (cost=4,095,914.34..4,095,914.42 rows=31 width=24) (actual time=61,754.300..61,754.305 rows=31 loops=1)

  • Buffers: shared hit=4764764 read=10
  • I/O Timings: read=12.559
2. 3.218 61,754.302 ↑ 6.0 31 1

Sort (cost=4,095,914.34..4,095,914.81 rows=187 width=24) (actual time=61,754.300..61,754.302 rows=31 loops=1)

  • Sort Key: pi.surname
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=4764764 read=10
  • I/O Timings: read=12.559
3. 4.173 61,751.084 ↓ 1.9 348 1

Nested Loop (cost=1.30..4,095,908.78 rows=187 width=24) (actual time=226.236..61,751.084 rows=348 loops=1)

  • Buffers: shared hit=4764761 read=10
  • I/O Timings: read=12.559
4. 1.054 46.163 ↓ 1.9 348 1

Nested Loop (cost=0.87..3,729.49 rows=187 width=12) (actual time=6.866..46.163 rows=348 loops=1)

  • Buffers: shared hit=4125 read=3
  • I/O Timings: read=10.768
5. 0.814 39.541 ↓ 1.9 348 1

Nested Loop (cost=0.58..3,660.39 rows=187 width=12) (actual time=4.717..39.541 rows=348 loops=1)

  • Buffers: shared hit=3082 read=2
  • I/O Timings: read=8.644
6. 2.437 28.287 ↓ 1.9 348 1

Nested Loop (cost=0.29..3,588.11 rows=187 width=16) (actual time=4.671..28.287 rows=348 loops=1)

  • Buffers: shared hit=2319 read=1
  • I/O Timings: read=1.777
7. 18.194 18.194 ↓ 1.9 348 1

Seq Scan on bso_rec_record brrc (cost=0.00..2,380.74 rows=187 width=12) (actual time=2.858..18.194 rows=348 loops=1)

  • Filter: (clinic_id = 30)
  • Rows Removed by Filter: 88308
  • Buffers: shared hit=1276
8. 7.656 7.656 ↑ 1.0 1 348

Index Scan using pim_employee_position_pk on pim_employee_position pep (cost=0.29..6.45 rows=1 width=12) (actual time=0.020..0.022 rows=1 loops=348)

  • Index Cond: (id = brrc.receiver_id)
  • Buffers: shared hit=1043 read=1
  • I/O Timings: read=1.777
9. 10.440 10.440 ↑ 1.0 1 348

Index Only Scan using pim_position_pk on pim_position pp (cost=0.29..0.38 rows=1 width=4) (actual time=0.028..0.030 rows=1 loops=348)

  • Index Cond: (id = pep.position_id)
  • Heap Fetches: 67
  • Buffers: shared hit=763 read=1
  • I/O Timings: read=6.867
10. 5.568 5.568 ↑ 1.0 1 348

Index Scan using pim_employee_pk on pim_employee pe (cost=0.29..0.36 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=348)

  • Index Cond: (id = pep.employee_id)
  • Buffers: shared hit=1043 read=1
  • I/O Timings: read=2.124
11. 6.960 6.960 ↑ 1.0 1 348

Index Scan using pim_individual_pk on pim_individual pi (cost=0.43..4.53 rows=1 width=20) (actual time=0.016..0.020 rows=1 loops=348)

  • Index Cond: (id = pe.individual_id)
  • Buffers: shared hit=1385 read=7
  • I/O Timings: read=1.791
12.          

SubPlan (for Nested Loop)

13. 8.700 61,693.788 ↑ 1.0 1 348

Aggregate (cost=21,878.76..21,878.76 rows=1 width=0) (actual time=177.281..177.281 rows=1 loops=348)

  • Buffers: shared hit=4759251
14. 37,592.700 61,685.088 ↑ 3.7 3 348

Hash Join (cost=6,511.46..21,878.73 rows=11 width=0) (actual time=132.535..177.256 rows=3 loops=348)

  • Hash Cond: (sd.range_id = brr.id)
  • Buffers: shared hit=4759251
15. 13,771.404 13,771.404 ↓ 1.0 364,009 348

Seq Scan on sickdoc sd (cost=0.00..14,037.84 rows=354,484 width=4) (actual time=0.003..39.573 rows=364,009 loops=348)

  • Buffers: shared hit=3651564
16. 4.872 10,320.984 ↑ 3.7 3 348

Hash (cost=6,511.33..6,511.33 rows=11 width=4) (actual time=29.658..29.658 rows=3 loops=348)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1107684
17. 10,316.112 10,316.112 ↑ 3.7 3 348

Seq Scan on bso_record_range brr (cost=0.00..6,511.33 rows=11 width=4) (actual time=20.438..29.644 rows=3 loops=348)

  • Filter: (rec_record_id = brrc.id)
  • Rows Removed by Filter: 267087
  • Buffers: shared hit=1107684
Planning time : 4.862 ms
Execution time : 61,754.434 ms