explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P3CE

Settings
# exclusive inclusive rows x rows loops node
1. 10.026 172,626.188 ↓ 3.8 71,244 1

Unique (cost=100,093.86..100,188.18 rows=18,865 width=12) (actual time=172,604.135..172,626.188 rows=71,244 loops=1)

  • Output: r.customer_id, r.id, r.edate
  • Buffers: shared hit=179,454 read=107,296, temp read=8,736 written=8,738
  • I/O Timings: read=170,816.028
2. 96.070 172,616.162 ↓ 6.3 119,670 1

Sort (cost=100,093.86..100,141.02 rows=18,865 width=12) (actual time=172,604.133..172,616.162 rows=119,670 loops=1)

  • Output: r.customer_id, r.id, r.edate
  • Sort Key: r.customer_id, r.edate DESC
  • Sort Method: external merge Disk: 2,472kB
  • Buffers: shared hit=179,454 read=107,296, temp read=8,736 written=8,738
  • I/O Timings: read=170,816.028
3. 109.684 172,520.092 ↓ 6.3 119,670 1

Gather (cost=69,578.10..98,754.12 rows=18,865 width=12) (actual time=171,702.730..172,520.092 rows=119,670 loops=1)

  • Output: r.customer_id, r.id, r.edate
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=179,448 read=107,296, temp read=8,427 written=8,427
  • I/O Timings: read=170,816.028
4. 768.365 172,410.408 ↓ 5.1 39,890 3 / 3

Hash Join (cost=68,578.10..95,867.62 rows=7,860 width=12) (actual time=171,565.708..172,410.408 rows=39,890 loops=3)

  • Output: r.customer_id, r.id, r.edate
  • Hash Cond: (p.id = r.customer_id)
  • Buffers: shared hit=179,448 read=107,296, temp read=8,427 written=8,427
  • I/O Timings: read=170,816.028
  • Worker 0: actual time=171,497.788..172373.565 rows=42,006 loops=1
  • Buffers: shared hit=56,011 read=39,805, temp read=2,886 written=2,886
  • I/O Timings: read=70,220.803
  • Worker 1: actual time=171,497.684..172351.194 rows=40,069 loops=1
  • Buffers: shared hit=59,306 read=36,401, temp read=2,855 written=2,855
  • I/O Timings: read=57,829.097
5. 76.984 76.984 ↑ 1.2 551,908 3 / 3

Parallel Seq Scan on public.pci_patient p (cost=0.00..24,623.85 rows=689,885 width=4) (actual time=0.104..76.984 rows=551,908 loops=3)

  • Output: p.id, p.created_dt, p.note, p.vip, p.rh_blood_group_id, p.ethnic_group_id, p.social_group_id, p.death_src_inf_id, p.empl_state_death_id, p.unidentified_dt, p.newborn_number, p.education_type_id, p.birthweight, p.aud_who, p.aud_when, p.aud_source, p.aud_who_create, p.aud_when_create, p.aud_source_create, p.employee_reg_death_position_id, p.last_fluorography_date
  • Buffers: shared read=17,725
  • I/O Timings: read=105.998
  • Worker 0: actual time=0.125..78.727 rows=567,565 loops=1
  • Buffers: shared read=6,131
  • I/O Timings: read=35.633
  • Worker 1: actual time=0.153..78.679 rows=552,121 loops=1
  • Buffers: shared read=6,022
  • I/O Timings: read=36.418
6. 124.023 171,565.059 ↓ 6.3 119,671 3 / 3

Hash (cost=68,342.29..68,342.29 rows=18,865 width=12) (actual time=171,565.058..171,565.059 rows=119,671 loops=3)

  • Output: r.customer_id, r.id, r.edate
  • Buckets: 131,072 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3,548kB
  • Buffers: shared hit=179,398 read=89,571, temp written=678
  • I/O Timings: read=170,710.030
  • Worker 0: actual time=171,497.158..171497.159 rows=119,671 loops=1
  • Buffers: shared hit=55,986 read=33,674, temp written=226
  • I/O Timings: read=70,185.170
  • Worker 1: actual time=171,496.870..171496.870 rows=119,671 loops=1
  • Buffers: shared hit=59,281 read=30,379, temp written=226
  • I/O Timings: read=57,792.679
7. 84.176 171,441.036 ↓ 6.3 119,671 3 / 3

Nested Loop (cost=460.86..68,342.29 rows=18,865 width=12) (actual time=69.135..171,441.036 rows=119,671 loops=3)

  • Output: r.customer_id, r.id, r.edate
  • Buffers: shared hit=179,398 read=89,571
  • I/O Timings: read=170,710.030
  • Worker 0: actual time=2.468..171373.926 rows=119,671 loops=1
  • Buffers: shared hit=55,986 read=33,674
  • I/O Timings: read=70,185.170
  • Worker 1: actual time=2.412..171374.998 rows=119,671 loops=1
  • Buffers: shared hit=59,281 read=30,379
  • I/O Timings: read=57,792.679
8. 0.007 54.712 ↓ 2.0 4 3 / 3

Unique (cost=460.30..460.31 rows=2 width=4) (actual time=54.703..54.712 rows=4 loops=3)

  • Output: s.id
  • Buffers: shared hit=270 read=32
  • I/O Timings: read=156.259
  • Worker 0: actual time=2.446..2.456 rows=4 loops=1
  • Buffers: shared hit=104
  • Worker 1: actual time=2.394..2.403 rows=4 loops=1
  • Buffers: shared hit=104
9. 0.041 54.705 ↓ 2.0 4 3 / 3

Sort (cost=460.30..460.30 rows=2 width=4) (actual time=54.703..54.705 rows=4 loops=3)

  • Output: s.id
  • Sort Key: s.id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=270 read=32
  • I/O Timings: read=156.259
  • Worker 0: actual time=2.445..2.448 rows=4 loops=1
  • Buffers: shared hit=104
  • Worker 1: actual time=2.394..2.397 rows=4 loops=1
  • Buffers: shared hit=104
10. 0.037 54.664 ↓ 2.0 4 3 / 3

Nested Loop (cost=141.87..460.29 rows=2 width=4) (actual time=27.847..54.664 rows=4 loops=3)

  • Output: s.id
  • Buffers: shared hit=256 read=32
  • I/O Timings: read=156.259
  • Worker 0: actual time=0.988..2.416 rows=4 loops=1
  • Buffers: shared hit=97
  • Worker 1: actual time=0.895..2.362 rows=4 loops=1
  • Buffers: shared hit=97
11. 38.910 38.910 ↑ 1.0 3 3 / 3

Index Only Scan using sr_srv_prototype_pk on public.sr_srv_prototype (cost=0.29..23.45 rows=3 width=4) (actual time=26.913..38.910 rows=3 loops=3)

  • Output: sr_srv_prototype.id
  • Index Cond: (sr_srv_prototype.id = ANY ('{787,788,10329}'::integer[]))
  • Heap Fetches: 9
  • Buffers: shared hit=34 read=4
  • I/O Timings: read=116.193
  • Worker 0: actual time=0.151..0.158 rows=3 loops=1
  • Buffers: shared hit=13
  • Worker 1: actual time=0.143..0.149 rows=3 loops=1
  • Buffers: shared hit=13
12. 13.347 15.717 ↑ 1.0 1 9 / 3

Bitmap Heap Scan on public.sr_service s (cost=141.59..145.60 rows=1 width=8) (actual time=4.804..5.239 rows=1 loops=9)

  • 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_fact, 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.prototype_id = sr_srv_prototype.id) AND (s.org_id = 33,668))
  • Heap Blocks: exact=4
  • Buffers: shared hit=222 read=28
  • I/O Timings: read=40.066
  • Worker 0: actual time=0.744..0.744 rows=1 loops=3
  • Buffers: shared hit=84
  • Worker 1: actual time=0.730..0.730 rows=1 loops=3
  • Buffers: shared hit=84
13. 0.012 2.370 ↓ 0.0 0 9 / 3

BitmapAnd (cost=141.59..141.59 rows=1 width=0) (actual time=0.790..0.790 rows=0 loops=9)

  • Buffers: shared hit=214 read=24
  • I/O Timings: read=0.115
  • Worker 0: actual time=0.740..0.740 rows=0 loops=3
  • Buffers: shared hit=80
  • Worker 1: actual time=0.727..0.727 rows=0 loops=3
  • Buffers: shared hit=80
14. 0.036 0.036 ↑ 4.5 13 9 / 3

Bitmap Index Scan on sr_service_prototype_id_idx (cost=0.00..4.86 rows=58 width=0) (actual time=0.012..0.012 rows=13 loops=9)

  • Index Cond: (s.prototype_id = sr_srv_prototype.id)
  • Buffers: shared hit=28 read=1
  • I/O Timings: read=0.010
  • Worker 0: actual time=0.007..0.007 rows=13 loops=3
  • Buffers: shared hit=10
  • Worker 1: actual time=0.005..0.005 rows=13 loops=3
  • Buffers: shared hit=10
15. 2.322 2.322 ↓ 1.0 7,423 9 / 3

Bitmap Index Scan on sr_service_org_id_idx (cost=0.00..134.67 rows=7,233 width=0) (actual time=0.774..0.774 rows=7,423 loops=9)

  • Index Cond: (s.org_id = 33,668)
  • Buffers: shared hit=186 read=23
  • I/O Timings: read=0.104
  • Worker 0: actual time=0.729..0.729 rows=7,423 loops=3
  • Buffers: shared hit=70
  • Worker 1: actual time=0.718..0.718 rows=7,423 loops=3
  • Buffers: shared hit=70
16. 171,302.148 171,302.148 ↓ 3.2 29,918 12 / 3

Index Scan using sr_srv_rendered_service_id_idx on public.sr_srv_rendered r (cost=0.56..33,846.66 rows=9,433 width=16) (actual time=12.351..42,825.537 rows=29,918 loops=12)

  • Output: r.id, r.bdate, r.comment, r.cost, r.duration, r.edate, r.is_rendered, r.quantity, r.total_cost, r.contract_id, r.customer_id, r.funding_id, r.res_group_id, r.service_id, r.duration_measure_unit_id, r.begin_time, r.prototype_id, r.org_id, r.price_list_id, r.cul, r.parent_id, r.payment_status_id, r.root_service_id, r.is_wholly_rendered, r.planned_date, r.planned_time, r.tooth_number, r.is_refused, r.aud_who, r.aud_when, r.aud_source, r.aud_who_create, r.aud_when_create, r.aud_source_create, r.is_amalgam_filling, r.is_mobile_medical_teams, r.close_date, r.complex_srv_id, r.for_upload, r.uid, r.is_need_consulting
  • Index Cond: (r.service_id = s.id)
  • Filter: r.is_rendered
  • Rows Removed by Filter: 2,286
  • Buffers: shared hit=179,128 read=89,539
  • I/O Timings: read=170,553.771
  • Worker 0: actual time=8.744..42821.795 rows=29,918 loops=4
  • Buffers: shared hit=55,882 read=33,674
  • I/O Timings: read=70,185.170
  • Worker 1: actual time=8.753..42822.276 rows=29,918 loops=4
  • Buffers: shared hit=59,177 read=30,379
  • I/O Timings: read=57,792.679