explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TN8b

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 78.036 ↑ 1.0 21 1

Limit (cost=5,432.30..5,499.90 rows=21 width=4) (actual time=65.056..78.036 rows=21 loops=1)

  • Output: sickdoc.end_dt
  • Buffers: shared hit=3826 read=1
  • I/O Timings: read=9.896
2.          

CTE s_continue

3. 0.453 47.821 ↓ 49.7 2,880 1

Unique (cost=5,421.55..5,431.59 rows=58 width=24) (actual time=47.082..47.821 rows=2,880 loops=1)

  • Output: c.parent_id, c.number, c.id, c.clinic_id
  • Buffers: shared hit=3439
4. 1.527 47.368 ↓ 1.4 2,881 1

Sort (cost=5,421.55..5,426.57 rows=2,009 width=24) (actual time=47.079..47.368 rows=2,881 loops=1)

  • Output: c.parent_id, c.number, c.id, c.clinic_id
  • Sort Key: c.parent_id
  • Sort Method: quicksort Memory: 322kB
  • Buffers: shared hit=3439
5. 45.841 45.841 ↓ 1.4 2,881 1

Seq Scan on sickdoc.sickdoc c (cost=0.00..5,311.33 rows=2,009 width=24) (actual time=0.064..45.841 rows=2,881 loops=1)

  • Output: c.parent_id, c.number, c.id, c.clinic_id
  • Filter: ((c.type_id = 2) AND (c.clinic_id = 164))
  • Rows Removed by Filter: 121941
  • Buffers: shared hit=3439
6. 7.070 78.026 ↑ 319.2 21 1

Nested Loop Left Join (cost=0.71..21,576.28 rows=6,703 width=4) (actual time=65.055..78.026 rows=21 loops=1)

  • Output: sickdoc.end_dt
  • Join Filter: (s_continue.parent_id = sickdoc.id)
  • Rows Removed by Join Filter: 60480
  • Buffers: shared hit=3826 read=1
  • I/O Timings: read=9.896
7. 0.050 17.049 ↑ 319.2 21 1

Nested Loop (cost=0.71..12,828.29 rows=6,703 width=8) (actual time=16.367..17.049 rows=21 loops=1)

  • Output: sickdoc.end_dt, sickdoc.id
  • Join Filter: (sickdoc.state_id = state.id)
  • Rows Removed by Join Filter: 81
  • Buffers: shared hit=387 read=1
  • I/O Timings: read=9.896
8. 0.041 16.978 ↑ 319.2 21 1

Nested Loop (cost=0.71..12,324.50 rows=6,703 width=12) (actual time=16.333..16.978 rows=21 loops=1)

  • Output: sickdoc.end_dt, sickdoc.id, sickdoc.state_id
  • Buffers: shared hit=386 read=1
  • I/O Timings: read=9.896
9. 0.028 0.620 ↑ 319.2 21 1

Nested Loop (cost=0.42..9,586.20 rows=6,703 width=12) (actual time=0.122..0.620 rows=21 loops=1)

  • Output: sickdoc.end_dt, sickdoc.id, sickdoc.state_id
  • Join Filter: (sickdoc.kind_id = kind.id)
  • Buffers: shared hit=342
10. 0.571 0.571 ↑ 957.6 21 1

Index Scan Backward using sickdoc_sickdoc_end_dt_idx on sickdoc.sickdoc (cost=0.42..9,283.53 rows=20,109 width=40) (actual time=0.104..0.571 rows=21 loops=1)

  • Output: sickdoc.id, sickdoc.begin_dt, sickdoc.case_id, sickdoc.clinic_id, sickdoc.days, sickdoc.end_dt, sickdoc.final_diagnosis_id, sickdoc.hospital_from_dt, sickdoc.hospital_to_dt, sickdoc.individual_id, sickdoc.initial_diagnosis_id, sickdoc.issue_dt, sickdoc.journal_id, sickdoc.kind_id, sickdoc.number, sickdoc.parent_id, sickdoc.ready_to_work_dt, sickdoc.ready_to_work_other_dt, sickdoc.ready_to_work_other_id, sickdoc.registrator_id, sickdoc.state_id, sickdoc.transfer_to_o_clinic, sickdoc.transfer_from_clinic, sickdoc.workplace_id, sickdoc.workplace_print, sickdoc.workplace_type_id, sickdoc.type_id, sickdoc.aud_who, sickdoc.aud_when, sickdoc.aud_source, sickdoc.aud_who_create, sickdoc.aud_when_create, sickdoc.aud_source_create, sickdoc.md_sicklist_id, sickdoc.registered_clinic_id, sickdoc.is_eln, sickdoc.is_print, sickdoc.range_id, sickdoc.ln_result_signature, sickdoc.hospital_breach_signature, sickdoc.fss_state_code, sickdoc.eln_hash, sickdoc.annual_reason_id, sickdoc.fss_annual_reason_comment, sickdoc.fss_annual_reason_id, sickdoc.print_clinic, sickdoc.parent_number_print, sickdoc.main_job_print_number, sickdoc.is_from_fss, sickdoc.print_clinic_ogrn, sickdoc.print_clinic_address
  • Filter: (sickdoc.clinic_id = 164)
  • Rows Removed by Filter: 590
  • Buffers: shared hit=341
11. 0.009 0.021 ↑ 1.0 1 21

Materialize (cost=0.00..1.04 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=21)

  • Output: kind.id
  • Buffers: shared hit=1
12. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on sickdoc.rf_kind kind (cost=0.00..1.04 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)

  • Output: kind.id
  • Filter: ((kind.code)::text = '1'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
13. 16.317 16.317 ↑ 1.0 1 21

Index Only Scan using sickdoc_extended_pk on sickdoc.sickdoc_extended se (cost=0.29..0.40 rows=1 width=4) (actual time=0.776..0.777 rows=1 loops=21)

  • Output: se.id
  • Index Cond: (se.id = sickdoc.id)
  • Heap Fetches: 2
  • Buffers: shared hit=44 read=1
  • I/O Timings: read=9.896
14. 0.011 0.021 ↑ 1.0 5 21

Materialize (cost=0.00..1.07 rows=5 width=4) (actual time=0.001..0.001 rows=5 loops=21)

  • Output: state.id
  • Buffers: shared hit=1
15. 0.010 0.010 ↑ 1.0 5 1

Seq Scan on public.md_sicklist_state state (cost=0.00..1.05 rows=5 width=4) (actual time=0.007..0.010 rows=5 loops=1)

  • Output: state.id
  • Buffers: shared hit=1
16. 53.907 53.907 ↓ 49.7 2,880 21

CTE Scan on s_continue (cost=0.00..1.16 rows=58 width=4) (actual time=2.242..2.567 rows=2,880 loops=21)

  • Output: s_continue.parent_id, s_continue.number, s_continue.id, s_continue.clinic_id
  • Buffers: shared hit=3439