explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UNDG

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 144,457.411 ↑ 1.0 31 1

Limit (cost=8,851,786.11..8,851,786.18 rows=31 width=45) (actual time=144,457.405..144,457.411 rows=31 loops=1)

  • Buffers: shared hit=6652224 read=15305
  • I/O Timings: read=225.340
2. 8.978 144,457.404 ↑ 6.3 31 1

Sort (cost=8,851,786.11..8,851,786.59 rows=195 width=45) (actual time=144,457.403..144,457.404 rows=31 loops=1)

  • Sort Key: ((SubPlan 3))
  • Sort Method: top-N heapsort Memory: 33kB
  • Buffers: shared hit=6652224 read=15305
  • I/O Timings: read=225.340
3. 66.768 144,448.426 ↓ 1.9 376 1

Nested Loop Left Join (cost=22,954.19..8,851,780.30 rows=195 width=45) (actual time=1,099.668..144,448.426 rows=376 loops=1)

  • Buffers: shared hit=6652221 read=15305
  • I/O Timings: read=225.340
4. 51.275 51.275 ↓ 1.9 376 1

Seq Scan on bso_rec_record j (cost=0.00..2,544.12 rows=195 width=37) (actual time=9.382..51.275 rows=376 loops=1)

  • Filter: (clinic_id = 30)
  • Rows Removed by Filter: 91468
  • Buffers: shared hit=402 read=984
  • I/O Timings: read=16.586
5. 33.840 123,024.568 ↑ 1.0 1 376

Aggregate (cost=22,954.19..22,954.20 rows=1 width=0) (actual time=327.192..327.193 rows=1 loops=376)

  • Buffers: shared hit=5371513 read=14311
  • I/O Timings: read=205.003
6. 78,545.272 122,990.728 ↑ 4.0 3 376

Hash Join (cost=6,905.77..22,954.16 rows=12 width=0) (actual time=243.185..327.103 rows=3 loops=376)

  • Hash Cond: (sd.range_id = brr.id)
  • Buffers: shared hit=5371513 read=14311
  • I/O Timings: read=205.003
7. 23,640.248 23,640.248 ↓ 1.0 377,867 376

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

  • Buffers: shared hit=4104009 read=10935
  • I/O Timings: read=158.123
8. 17.672 20,805.208 ↑ 4.0 3 376

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1267504 read=3376
  • I/O Timings: read=46.880
9. 20,787.536 20,787.536 ↑ 4.0 3 376

Seq Scan on bso_record_range brr (cost=0.00..6,905.62 rows=12 width=4) (actual time=37.057..55.286 rows=3 loops=376)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280280
  • Buffers: shared hit=1267504 read=3376
  • I/O Timings: read=46.880
10.          

SubPlan (for Nested Loop Left Join)

11. 26.696 20,796.184 ↑ 1.0 1 376

Result (cost=6,905.65..6,905.67 rows=1 width=0) (actual time=55.308..55.309 rows=1 loops=376)

  • Buffers: shared hit=1270880
12.          

Initplan (for Result)

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

Seq Scan on bso_record_range r (cost=0.00..6,905.65 rows=12 width=22) (actual time=37.128..55.238 rows=3 loops=376)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280280
  • Buffers: shared hit=1270880
14. 3.008 60.160 ↑ 1.0 1 376

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

  • Buffers: shared hit=4883 read=5
  • I/O Timings: read=0.164
15. 13.536 57.152 ↑ 1.0 1 376

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

  • Buffers: shared hit=4883 read=5
  • I/O Timings: read=0.164
16. 5.264 35.344 ↑ 1.0 1 376

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

  • Buffers: shared hit=3382 read=2
  • I/O Timings: read=0.074
17. 7.144 22.936 ↑ 1.0 1 376

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

  • Buffers: shared hit=2254 read=2
  • I/O Timings: read=0.074
18. 9.024 9.024 ↑ 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.024..0.024 rows=1 loops=376)

  • Index Cond: (id = j.receiver_id)
  • Buffers: shared hit=1127 read=1
  • I/O Timings: read=0.040
19. 6.768 6.768 ↑ 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.018..0.018 rows=1 loops=376)

  • Index Cond: (id = ep.position_id)
  • Buffers: shared hit=1127 read=1
  • I/O Timings: read=0.034
20. 7.144 7.144 ↑ 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.019..0.019 rows=1 loops=376)

  • Index Cond: (id = ep.employee_id)
  • Buffers: shared hit=1128
21. 8.272 8.272 ↑ 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.022..0.022 rows=1 loops=376)

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

Nested Loop Semi Join (cost=23.77..15,491.28 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,546.62 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. 284.102 449.471 ↓ 0.0 0 1

Nested Loop Semi Join (cost=23.77..1,394,232.30 rows=3,482 width=4) (actual time=449.471..449.471 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=4543 read=5
  • I/O Timings: read=3.587
28. 143.310 143.310 ↑ 242.9 129 1

Seq Scan on bso_record_range brr_2 (cost=0.00..11,841.50 rows=31,339 width=26) (actual time=0.075..143.310 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: 280154
  • Buffers: shared hit=3380
29. 13.478 22.059 ↓ 1.4 1,342 129

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

  • Buffers: shared hit=1163 read=5
  • I/O Timings: read=3.587
30. 4.532 8.581 ↓ 1.4 1,342 1

Bitmap Heap Scan on sickdoc s_1 (cost=23.77..2,890.44 rows=943 width=12) (actual time=4.276..8.581 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=1163 read=5
  • I/O Timings: read=3.587
31. 4.049 4.049 ↓ 1.4 1,344 1

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

  • Index Cond: (clinic_id = 30)
  • Buffers: shared hit=3 read=5
  • I/O Timings: read=3.587
Planning time : 13.647 ms
Execution time : 144,458.802 ms