explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2xJD

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 724.165 ↓ 5.0 5 1

Limit (cost=24,822.45..24,822.46 rows=1 width=191) (actual time=724.105..724.165 rows=5 loops=1)

  • Output: s.id, (CASE WHEN (s.is_complex IS TRUE) THEN (('[+]'::text || (s.name)::text))::character varying ELSE s.name END), s.terms, s.org_id, s.duration
  • Buffers: shared hit=18464 read=306 dirtied=48
  • I/O Timings: read=592.925
2.          

Initplan (for Limit)

3. 0.000 0.000 ↓ 0.0 0

Index Scan using sec_user_role_user_id_idx on public.sec_user_role (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: (sec_user_role.user_id = 872)
  • Filter: (sec_user_role.role_id = 135)
4. 0.045 724.162 ↓ 5.0 5 1

Unique (cost=24,814.14..24,814.16 rows=1 width=191) (actual time=724.102..724.162 rows=5 loops=1)

  • Output: s.id, (CASE WHEN (s.is_complex IS TRUE) THEN (('[+]'::text || (s.name)::text))::character varying ELSE s.name END), s.terms, s.org_id, s.duration
  • Buffers: shared hit=18464 read=306 dirtied=48
  • I/O Timings: read=592.925
5. 1.014 724.117 ↓ 217.0 217 1

Sort (cost=24,814.14..24,814.14 rows=1 width=191) (actual time=724.101..724.117 rows=217 loops=1)

  • Output: s.id, (CASE WHEN (s.is_complex IS TRUE) THEN (('[+]'::text || (s.name)::text))::character varying ELSE s.name END), s.terms, s.org_id, s.duration
  • Sort Key: (CASE WHEN (s.is_complex IS TRUE) THEN (('[+]'::text || (s.name)::text))::character varying ELSE s.name END), s.id, s.terms, s.duration
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=18464 read=306 dirtied=48
  • I/O Timings: read=592.925
6. 1.137 723.103 ↓ 217.0 217 1

Nested Loop Left Join (cost=209.39..24,814.13 rows=1 width=191) (actual time=18.769..723.103 rows=217 loops=1)

  • Output: s.id, CASE WHEN (s.is_complex IS TRUE) THEN (('[+]'::text || (s.name)::text))::character varying ELSE s.name END, s.terms, s.org_id, s.duration
  • Join Filter: (srgs.srv_type_id = s.id)
  • Rows Removed by Join Filter: 63
  • Filter: CASE WHEN ((102 = ANY ('{12,40}'::integer[])) AND $0) THEN srgs.referral_required ELSE true END
  • Buffers: shared hit=18458 read=306 dirtied=48
  • I/O Timings: read=592.925
7. 0.000 196.870 ↓ 216.0 216 1

Nested Loop (cost=208.96..24,804.81 rows=1 width=195) (actual time=9.303..196.870 rows=216 loops=1)

  • Output: aps.res_group_id, s.id, s.is_complex, s.name, s.terms, s.org_id, s.duration
  • Buffers: shared hit=17647 read=215 dirtied=47
  • I/O Timings: read=71.558
8. 72.687 78.637 ↓ 4,225.0 4,225 1

Bitmap Heap Scan on public.sr_service s (cost=208.96..24,563.57 rows=1 width=195) (actual time=7.348..78.637 rows=4,225 loops=1)

  • Output: s.id, s.code, s.cul, s.is_death, s.duration, s.is_fictitious, s.is_independent, s.is_multuplicity, s.name, s.terms, s.org_id, s.category_id, s.duration_unit_id, s.prototype_id, s.type_id, s.from_dt, s.to_dt, s.is_repeated, s.is_inherit_protocol, s.is_expendable_materials, s.is_actual_cul, s.is_paraclinical, s.is_complex, s.accounting_id, s.is_autocopy_diagnoses, s.is_stomat, s.srg_dif_type_id, s.srg_opr_kind_id, s.is_need_anatomic_zone, s.aud_who, s.aud_when, s.aud_source, s.aud_who_create, s.aud_when_create, s.aud_source_create, s.is_filling, s.is_extraction, s.is_need_close_date, s.is_vmp, s.short_name
  • Recheck Cond: (s.org_id = 102)
  • Filter: (lower(('[+]'::text || (s.name)::text)) ~~ '%терап%'::text)
  • Rows Removed by Filter: 5965
  • Heap Blocks: exact=5005
  • Buffers: shared hit=5007 read=31
  • I/O Timings: read=3.690
9. 5.950 5.950 ↑ 1.2 10,215 1

Bitmap Index Scan on sr_service_org_id_idx (cost=0.00..208.96 rows=11,804 width=0) (actual time=5.950..5.950 rows=10,215 loops=1)

  • Index Cond: (s.org_id = 102)
  • Buffers: shared hit=2 read=31
  • I/O Timings: read=3.690
10. 12.675 118.300 ↓ 0.0 0 4,225

Append (cost=0.00..241.13 rows=11 width=8) (actual time=0.011..0.028 rows=0 loops=4,225)

  • Buffers: shared hit=12640 read=184 dirtied=47
  • I/O Timings: read=67.868
11. 0.000 0.000 ↓ 0.0 0 4,225

Seq Scan on public.sr_scgenerated aps (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=4,225)

  • Output: aps.service_id, aps.res_group_id
  • Filter: ((aps.date >= '2019-10-07'::date) AND (aps.date <= '2019-10-13'::date) AND (aps.source_id = 1) AND (s.id = aps.service_id))
12. 76.050 105.625 ↓ 0.0 0 4,225

Bitmap Heap Scan on schedule.sr_scgenerated_y2019_m10_r38 aps_1 (cost=5.23..241.13 rows=10 width=8) (actual time=0.008..0.025 rows=0 loops=4,225)

  • Output: aps_1.service_id, aps_1.res_group_id
  • Recheck Cond: ((aps_1.service_id = s.id) AND (aps_1.date >= '2019-10-07'::date) AND (aps_1.date <= '2019-10-13'::date))
  • Filter: (aps_1.source_id = 1)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=144
  • Buffers: shared hit=12640 read=184 dirtied=47
  • I/O Timings: read=67.868
13. 29.575 29.575 ↓ 0.0 0 4,225

Bitmap Index Scan on sr_scgenerated_y2019_m10_r38_srv_ix (cost=0.00..5.22 rows=64 width=0) (actual time=0.007..0.007 rows=0 loops=4,225)

  • Index Cond: ((aps_1.service_id = s.id) AND (aps_1.date >= '2019-10-07'::date) AND (aps_1.date <= '2019-10-13'::date))
  • Buffers: shared hit=12635 read=45
  • I/O Timings: read=20.841
14. 525.096 525.096 ↑ 63.0 1 216

Index Scan using sr_res_group_service_group_id_idx on public.sr_res_group_service srgs (cost=0.43..8.37 rows=63 width=9) (actual time=2.256..2.431 rows=1 loops=216)

  • Output: srgs.id, srgs.bdatetime, srgs.edatetime, srgs.power, srgs.group_id, srgs.srv_type_id, srgs.moderation_required, srgs.patient_multiple_appointment_per_day, srgs.referral_required, srgs.default_service, srgs.aud_who, srgs.aud_when, srgs.aud_source, srgs.aud_who_create, srgs.aud_when_create, srgs.aud_source_create
  • Index Cond: (srgs.group_id = aps.res_group_id)
  • Buffers: shared hit=811 read=91 dirtied=1
  • I/O Timings: read=521.367
Planning time : 2,511.791 ms
Execution time : 724.423 ms