explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ukwj

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 143,337.785 ↑ 1.0 31 1

Limit (cost=8,838,098.67..8,838,098.74 rows=31 width=45) (actual time=143,337.775..143,337.785 rows=31 loops=1)

  • Buffers: shared hit=6646421 read=14330 written=27
  • I/O Timings: read=186.018 write=2.150
2. 3.120 143,337.781 ↑ 6.3 31 1

Sort (cost=8,838,098.67..8,838,099.15 rows=195 width=45) (actual time=143,337.772..143,337.781 rows=31 loops=1)

  • Sort Key: j.date_in
  • Sort Method: top-N heapsort Memory: 29kB
  • Buffers: shared hit=6646421 read=14330 written=27
  • I/O Timings: read=186.018 write=2.150
3. 81.497 143,334.661 ↓ 1.9 376 1

Nested Loop Left Join (cost=22,935.80..8,838,092.86 rows=195 width=45) (actual time=1,175.152..143,334.661 rows=376 loops=1)

  • Buffers: shared hit=6646418 read=14330 written=27
  • I/O Timings: read=186.018 write=2.150
4. 45.879 45.879 ↓ 1.9 376 1

Seq Scan on bso_rec_record j (cost=0.00..2,542.29 rows=195 width=37) (actual time=9.570..45.879 rows=376 loops=1)

  • Filter: (clinic_id = 30)
  • Rows Removed by Filter: 91412
  • Buffers: shared hit=792 read=593
  • I/O Timings: read=8.295
5. 69.184 122,394.016 ↑ 1.0 1 376

Aggregate (cost=22,935.80..22,935.81 rows=1 width=0) (actual time=325.516..325.516 rows=1 loops=376)

  • Buffers: shared hit=5368715 read=13725 written=27
  • I/O Timings: read=175.404 write=2.150
6. 78,027.896 122,324.832 ↑ 4.0 3 376

Hash Join (cost=6,887.39..22,935.77 rows=12 width=0) (actual time=240.437..325.332 rows=3 loops=376)

  • Hash Cond: (sd.range_id = brr.id)
  • Buffers: shared hit=5368715 read=13725 written=27
  • I/O Timings: read=175.404 write=2.150
7. 23,990.304 23,990.304 ↓ 1.0 377,636 376

Seq Scan on sickdoc sd (cost=0.00..14,656.19 rows=371,219 width=4) (actual time=0.006..63.804 rows=377,636 loops=376)

  • Buffers: shared hit=4104462 read=10482 written=27
  • I/O Timings: read=132.245 write=2.150
8. 26.320 20,306.632 ↑ 4.0 3 376

Hash (cost=6,887.24..6,887.24 rows=12 width=4) (actual time=54.007..54.007 rows=3 loops=376)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1264253 read=3243
  • I/O Timings: read=43.159
9. 20,280.312 20,280.312 ↑ 4.0 3 376

Seq Scan on bso_record_range brr (cost=0.00..6,887.24 rows=12 width=4) (actual time=36.579..53.937 rows=3 loops=376)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 279877
  • Buffers: shared hit=1264253 read=3243
  • I/O Timings: read=43.159
10.          

SubPlan (for Nested Loop Left Join)

11. 30.456 20,287.080 ↑ 1.0 1 376

Result (cost=6,887.27..6,887.28 rows=1 width=0) (actual time=53.954..53.955 rows=1 loops=376)

  • Buffers: shared hit=1267496
12.          

Initplan (for Result)

13. 20,256.624 20,256.624 ↑ 4.0 3 376

Seq Scan on bso_record_range r (cost=0.00..6,887.27 rows=12 width=22) (actual time=36.703..53.874 rows=3 loops=376)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 279877
  • Buffers: shared hit=1267496
14. 2.256 64.672 ↑ 1.0 1 376

Limit (cost=1.30..29.51 rows=1 width=104) (actual time=0.172..0.172 rows=1 loops=376)

  • Buffers: shared hit=4882 read=6
  • I/O Timings: read=0.170
15. 15.040 62.416 ↑ 1.0 1 376

Nested Loop (cost=1.30..29.51 rows=1 width=104) (actual time=0.166..0.166 rows=1 loops=376)

  • Buffers: shared hit=4882 read=6
  • I/O Timings: read=0.170
16. 4.888 39.480 ↑ 1.0 1 376

Nested Loop (cost=0.87..24.95 rows=1 width=58) (actual time=0.105..0.105 rows=1 loops=376)

  • Buffers: shared hit=3383 read=1
  • I/O Timings: read=0.025
17. 7.520 24.064 ↑ 1.0 1 376

Nested Loop (cost=0.58..16.63 rows=1 width=58) (actual time=0.064..0.064 rows=1 loops=376)

  • Buffers: shared hit=2256
18. 10.528 10.528 ↑ 1.0 1 376

Index Scan using pim_employee_position_pk on pim_employee_position ep (cost=0.29..8.31 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=376)

  • Index Cond: (id = j.receiver_id)
  • Buffers: shared hit=1128
19. 6.016 6.016 ↑ 1.0 1 376

Index Scan using pim_position_pk on pim_position pp (cost=0.29..8.31 rows=1 width=58) (actual time=0.016..0.016 rows=1 loops=376)

  • Index Cond: (id = ep.position_id)
  • Buffers: shared hit=1128
20. 10.528 10.528 ↑ 1.0 1 376

Index Scan using pim_employee_pk on pim_employee e (cost=0.29..8.31 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=376)

  • Index Cond: (id = ep.employee_id)
  • Buffers: shared hit=1127 read=1
  • I/O Timings: read=0.025
21. 7.896 7.896 ↑ 1.0 1 376

Index Scan using pim_individual_pk on pim_individual i (cost=0.43..4.54 rows=1 width=54) (actual time=0.021..0.021 rows=1 loops=376)

  • Index Cond: (id = e.individual_id)
  • Buffers: shared hit=1499 read=5
  • I/O Timings: read=0.145
22. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=23.77..15,457.88 rows=1 width=0) (never executed)

  • Join Filter: ((("left"(replace((s.number)::text, ' '::text, ''::text), 9))::bigint >= (replace((brr_1.start_num)::text, ' '::text, ''::text))::bigint) AND (("left"(replace((s.number)::text, ' '::text, ''::text), 9))::bigint <= (replace((COALESCE(brr_1.end_num, brr_1.start_num))::text, ' '::text, ''::text))::bigint))
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on bso_record_range brr_1 (cost=0.00..12,513.22 rows=1 width=22) (never executed)

  • Filter: ((rec_record_id = j.id) AND ((replace((start_num)::text, ' '::text, ''::text))::bigint <= 999999999) AND ((replace((COALESCE(end_num, start_num))::text, ' '::text, ''::text))::bigint <= 999999999))
24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=23.77..2,895.15 rows=943 width=12) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sickdoc s (cost=23.77..2,890.44 rows=943 width=12) (never executed)

  • Recheck Cond: (clinic_id = 30)
  • Filter: ("left"((number)::text, 9) <> ''::text)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on sickdoc_sickdoc_clinic_idx (cost=0.00..23.53 rows=948 width=0) (never executed)

  • Index Cond: (clinic_id = 30)
27. 289.652 461.517 ↓ 0.0 0 1

Nested Loop Semi Join (cost=23.77..1,390,486.76 rows=3,473 width=4) (actual time=461.517..461.517 rows=0 loops=1)

  • Join Filter: ((("left"(replace((s_1.number)::text, ' '::text, ''::text), 9))::bigint >= (replace((brr_2.start_num)::text, ' '::text, ''::text))::bigint) AND (("left"(replace((s_1.number)::text, ' '::text, ''::text), 9))::bigint <= (replace((COALESCE(brr_2.end_num, brr_2.start_num))::text, ' '::text, ''::text))::bigint))
  • Rows Removed by Join Filter: 173118
  • Buffers: shared hit=4533 read=6
  • I/O Timings: read=2.149
28. 142.711 142.711 ↑ 242.3 129 1

Seq Scan on bso_record_range brr_2 (cost=0.00..11,809.97 rows=31,255 width=26) (actual time=0.055..142.711 rows=129 loops=1)

  • Filter: (((replace((start_num)::text, ' '::text, ''::text))::bigint <= 999999999) AND ((replace((COALESCE(end_num, start_num))::text, ' '::text, ''::text))::bigint <= 999999999))
  • Rows Removed by Filter: 279751
  • Buffers: shared hit=3371
29. 14.135 29.154 ↓ 1.4 1,342 129

Materialize (cost=23.77..2,895.15 rows=943 width=12) (actual time=0.022..0.226 rows=1,342 loops=129)

  • Buffers: shared hit=1162 read=6
  • I/O Timings: read=2.149
30. 12.409 15.019 ↓ 1.4 1,342 1

Bitmap Heap Scan on sickdoc s_1 (cost=23.77..2,890.44 rows=943 width=12) (actual time=2.830..15.019 rows=1,342 loops=1)

  • Recheck Cond: (clinic_id = 30)
  • Filter: ("left"((number)::text, 9) <> ''::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1160
  • Buffers: shared hit=1162 read=6
  • I/O Timings: read=2.149
31. 2.610 2.610 ↓ 1.4 1,344 1

Bitmap Index Scan on sickdoc_sickdoc_clinic_idx (cost=0.00..23.53 rows=948 width=0) (actual time=2.610..2.610 rows=1,344 loops=1)

  • Index Cond: (clinic_id = 30)
  • Buffers: shared hit=2 read=6
  • I/O Timings: read=2.149
Planning time : 13.230 ms
Execution time : 143,338.711 ms