explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IFz

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 11,907.875 ↑ 1.0 31 1

Limit (cost=22,954.19..1,426,511.16 rows=31 width=45) (actual time=837.265..11,907.875 rows=31 loops=1)

  • Buffers: shared hit=554289 read=85
  • I/O Timings: read=1.494
2. 6.019 11,907.844 ↑ 6.3 31 1

Nested Loop Left Join (cost=22,954.19..8,851,780.30 rows=195 width=45) (actual time=837.263..11,907.844 rows=31 loops=1)

  • Buffers: shared hit=554289 read=85
  • I/O Timings: read=1.494
3. 10.546 10.546 ↑ 6.3 31 1

Seq Scan on bso_rec_record j (cost=0.00..2,544.12 rows=195 width=37) (actual time=3.907..10.546 rows=31 loops=1)

  • Filter: (clinic_id = 30)
  • Rows Removed by Filter: 41859
  • Buffers: shared hit=521 read=78
  • I/O Timings: read=1.305
4. 1.364 9,816.305 ↑ 1.0 1 31

Aggregate (cost=22,954.19..22,954.20 rows=1 width=0) (actual time=316.654..316.655 rows=1 loops=31)

  • Buffers: shared hit=444043 read=1
  • I/O Timings: read=0.054
5. 6,329.270 9,814.941 ↑ 12.0 1 31

Hash Join (cost=6,905.77..22,954.16 rows=12 width=0) (actual time=165.786..316.611 rows=1 loops=31)

  • Hash Cond: (sd.range_id = brr.id)
  • Buffers: shared hit=444043 read=1
  • I/O Timings: read=0.054
6. 1,877.298 1,877.298 ↓ 1.0 377,869 31

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

  • Buffers: shared hit=339263 read=1
  • I/O Timings: read=0.054
7. 0.589 1,608.373 ↑ 12.0 1 31

Hash (cost=6,905.62..6,905.62 rows=12 width=4) (actual time=51.883..51.883 rows=1 loops=31)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=104780
8. 1,607.784 1,607.784 ↑ 12.0 1 31

Seq Scan on bso_record_range brr (cost=0.00..6,905.62 rows=12 width=4) (actual time=20.160..51.864 rows=1 loops=31)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280282
  • Buffers: shared hit=104780
9.          

SubPlan (for Nested Loop Left Join)

10. 2.046 1,622.602 ↑ 1.0 1 31

Result (cost=6,905.65..6,905.67 rows=1 width=0) (actual time=52.342..52.342 rows=1 loops=31)

  • Buffers: shared hit=104780
11.          

Initplan (for Result)

12. 1,620.556 1,620.556 ↑ 12.0 1 31

Seq Scan on bso_record_range r (cost=0.00..6,905.65 rows=12 width=22) (actual time=20.199..52.276 rows=1 loops=31)

  • Filter: (rec_record_id = j.id)
  • Rows Removed by Filter: 280282
  • Buffers: shared hit=104780
13. 0.434 6.479 ↑ 1.0 1 31

Limit (cost=1.30..29.51 rows=1 width=104) (actual time=0.208..0.209 rows=1 loops=31)

  • Buffers: shared hit=402 read=1
  • I/O Timings: read=0.035
14. 1.457 6.045 ↑ 1.0 1 31

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

  • Buffers: shared hit=402 read=1
  • I/O Timings: read=0.035
15. 0.527 3.720 ↑ 1.0 1 31

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

  • Buffers: shared hit=279
16. 0.868 2.418 ↑ 1.0 1 31

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

  • Buffers: shared hit=186
17. 0.868 0.868 ↑ 1.0 1 31

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=31)

  • Index Cond: (id = j.receiver_id)
  • Buffers: shared hit=93
18. 0.682 0.682 ↑ 1.0 1 31

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

  • Index Cond: (id = ep.position_id)
  • Buffers: shared hit=93
19. 0.775 0.775 ↑ 1.0 1 31

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

  • Index Cond: (id = ep.employee_id)
  • Buffers: shared hit=93
20. 0.868 0.868 ↑ 1.0 1 31

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

  • Index Cond: (id = e.individual_id)
  • Buffers: shared hit=123 read=1
  • I/O Timings: read=0.035
21. 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))
22. 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))
23. 0.000 0.000 ↓ 0.0 0

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

24. 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)
25. 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)
26. 280.724 445.893 ↓ 0.0 0 1

Nested Loop Semi Join (cost=23.77..1,394,232.30 rows=3,482 width=4) (actual time=445.893..445.893 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=0.100
27. 146.335 146.335 ↑ 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.105..146.335 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
28. 13.640 18.834 ↓ 1.4 1,342 129

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

  • Buffers: shared hit=1163 read=5
  • I/O Timings: read=0.100
29. 4.611 5.194 ↓ 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.818..5.194 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=0.100
30. 0.583 0.583 ↓ 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.583..0.583 rows=1,344 loops=1)

  • Index Cond: (clinic_id = 30)
  • Buffers: shared hit=3 read=5
  • I/O Timings: read=0.100
Planning time : 4.456 ms
Execution time : 11,908.776 ms