explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bg3V

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 140,182.163 ↑ 1.0 31 1

Limit (cost=8,854,086.10..8,854,086.17 rows=31 width=149) (actual time=140,182.156..140,182.163 rows=31 loops=1)

  • Buffers: shared hit=6671106 read=1308
  • I/O Timings: read=24.580
2. 6.494 140,182.157 ↑ 6.3 31 1

Sort (cost=8,854,086.10..8,854,086.58 rows=195 width=149) (actual time=140,182.154..140,182.157 rows=31 loops=1)

  • Sort Key: (concat_ws(' '::text, pi.surname, ("left"((pi.name)::text, 1) || '.'::text), ("left"((pi.patr_name)::text, 1) || '.,'::text), pp.name))
  • Sort Method: top-N heapsort Memory: 33kB
  • Buffers: shared hit=6671106 read=1308
  • I/O Timings: read=24.580
3. 55.695 140,175.663 ↓ 1.9 376 1

Nested Loop Left Join (cost=22,955.49..8,854,080.29 rows=195 width=149) (actual time=817.759..140,175.663 rows=376 loops=1)

  • Buffers: shared hit=6671106 read=1308
  • I/O Timings: read=24.580
4. 2.391 90.823 ↓ 1.9 376 1

Nested Loop (cost=1.30..4,841.68 rows=195 width=141) (actual time=9.797..90.823 rows=376 loops=1)

  • Buffers: shared hit=4966 read=1308
  • I/O Timings: read=24.580
5. 2.484 83.920 ↓ 1.9 376 1

Nested Loop (cost=0.87..3,954.89 rows=195 width=95) (actual time=9.780..83.920 rows=376 loops=1)

  • Buffers: shared hit=3463 read=1307
  • I/O Timings: read=24.556
6. 3.212 77.300 ↓ 1.9 376 1

Nested Loop (cost=0.58..3,882.87 rows=195 width=95) (actual time=9.766..77.300 rows=376 loops=1)

  • Buffers: shared hit=2335 read=1307
  • I/O Timings: read=24.556
7. 3.265 70.704 ↓ 1.9 376 1

Nested Loop (cost=0.29..3,794.04 rows=195 width=45) (actual time=9.750..70.704 rows=376 loops=1)

  • Buffers: shared hit=1208 read=1306
  • I/O Timings: read=24.510
8. 63.303 63.303 ↓ 1.9 376 1

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

  • Filter: (clinic_id = 30)
  • Rows Removed by Filter: 91468
  • Buffers: shared hit=80 read=1306
  • I/O Timings: read=24.510
9. 4.136 4.136 ↑ 1.0 1 376

Index Scan using pim_employee_position_pk on pim_employee_position pep (cost=0.29..6.40 rows=1 width=12) (actual time=0.008..0.011 rows=1 loops=376)

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

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

  • Index Cond: (id = pep.position_id)
  • Buffers: shared hit=1127 read=1
  • I/O Timings: read=0.046
11. 4.136 4.136 ↑ 1.0 1 376

Index Scan using pim_employee_pk on pim_employee pe (cost=0.29..0.36 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=376)

  • Index Cond: (id = pep.employee_id)
  • Buffers: shared hit=1128
12. 4.512 4.512 ↑ 1.0 1 376

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

  • Index Cond: (id = pe.individual_id)
  • Buffers: shared hit=1503 read=1
  • I/O Timings: read=0.024
13. 18.424 120,056.424 ↑ 1.0 1 376

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

  • Buffers: shared hit=5385824
14. 75,997.496 120,038.000 ↑ 4.0 3 376

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

  • Hash Cond: (sd.range_id = brr.id)
  • Buffers: shared hit=5385824
15. 23,344.712 23,344.712 ↓ 1.0 377,869 376

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

  • Buffers: shared hit=4114944
16. 13.160 20,695.792 ↑ 4.0 3 376

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1270880
17. 20,682.632 20,682.632 ↑ 4.0 3 376

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

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280280
  • Buffers: shared hit=1270880
18.          

SubPlan (for Nested Loop Left Join)

19. 23.688 19,480.184 ↑ 1.0 1 376

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

  • Buffers: shared hit=1270880
20.          

Initplan (for Result)

21. 19,456.496 19,456.496 ↑ 4.0 3 376

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

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280280
  • Buffers: shared hit=1270880
22. 2.256 57.904 ↑ 1.0 1 376

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

  • Buffers: shared hit=4888
23. 14.288 55.648 ↑ 1.0 1 376

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

  • Buffers: shared hit=4888
24. 4.512 33.464 ↑ 1.0 1 376

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

  • Buffers: shared hit=3384
25. 7.144 22.184 ↑ 1.0 1 376

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

  • Buffers: shared hit=2256
26. 8.648 8.648 ↑ 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.023..0.023 rows=1 loops=376)

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

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

  • Index Cond: (id = ep.position_id)
  • Buffers: shared hit=1128
28. 6.768 6.768 ↑ 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.018..0.018 rows=1 loops=376)

  • Index Cond: (id = ep.employee_id)
  • Buffers: shared hit=1128
29. 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=1504
30. 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))
31. 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))
32. 0.000 0.000 ↓ 0.0 0

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

33. 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)
34. 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)
35. 276.548 434.633 ↓ 0.0 0 1

Nested Loop Semi Join (cost=23.77..1,394,232.30 rows=3,482 width=4) (actual time=434.633..434.633 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=4548
36. 139.509 139.509 ↑ 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.124..139.509 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
37. 13.784 18.576 ↓ 1.4 1,342 129

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

  • Buffers: shared hit=1168
38. 4.380 4.792 ↓ 1.4 1,342 1

Bitmap Heap Scan on sickdoc s_1 (cost=23.77..2,890.44 rows=943 width=12) (actual time=0.648..4.792 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=1168
39. 0.412 0.412 ↓ 1.4 1,344 1

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

  • Index Cond: (clinic_id = 30)
  • Buffers: shared hit=8
Planning time : 7.021 ms
Execution time : 140,183.674 ms