explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7NHW

Settings
# exclusive inclusive rows x rows loops node
1. 296.705 40,529.922 ↑ 21.6 14,828 1

Hash Join (cost=2,944,886.94..2,988,060.77 rows=320,950 width=65) (actual time=39,964.193..40,529.922 rows=14,828 loops=1)

  • Output: patient0_.id, patient0_.birthweight, patient0_.rh_blood_group_id, patient0_.created_dt, patient0_.death_src_inf_id, patient0_.education_type_id, patient0_.empl_state_death_id, patient0_.employee_reg_death_position_id, patient0_.ethnic_group_id, patient0_.aud_when, patient0_.newborn_number, patient0_.note, patient0_.social_group_id, patient0_.unidentified_dt, patient0_.vip
  • Hash Cond: (patient0_.id = serviceren1_.customer_id)
  • Buffers: shared hit=1616698 read=86875 dirtied=1
  • I/O Timings: read=765.913
2. 271.848 271.848 ↓ 1.0 1,456,557 1

Seq Scan on public.pci_patient patient0_ (cost=0.00..34,576.23 rows=1,456,523 width=65) (actual time=0.016..271.848 rows=1,456,557 loops=1)

  • Output: patient0_.id, patient0_.created_dt, patient0_.note, patient0_.vip, patient0_.rh_blood_group_id, patient0_.ethnic_group_id, patient0_.social_group_id, patient0_.death_src_inf_id, patient0_.empl_state_death_id, patient0_.unidentified_dt, patient0_.newborn_number, patient0_.education_type_id, patient0_.birthweight, patient0_.aud_who, patient0_.aud_when, patient0_.aud_source, patient0_.aud_who_create, patient0_.aud_when_create, patient0_.aud_source_create, patient0_.employee_reg_death_position_id, patient0_.last_fluorography_date
  • Buffers: shared hit=20011
3. 5.813 39,961.369 ↑ 21.1 14,828 1

Hash (cost=2,940,967.39..2,940,967.39 rows=313,564 width=4) (actual time=39,961.369..39,961.369 rows=14,828 loops=1)

  • Output: serviceren1_.customer_id
  • Buckets: 524288 Batches: 1 Memory Usage: 4618kB
  • Buffers: shared hit=1596687 read=86875 dirtied=1
  • I/O Timings: read=765.913
4. 115.295 39,955.556 ↑ 21.1 14,828 1

HashAggregate (cost=2,937,831.75..2,940,967.39 rows=313,564 width=4) (actual time=39,948.954..39,955.556 rows=14,828 loops=1)

  • Output: serviceren1_.customer_id
  • Group Key: serviceren1_.customer_id
  • Buffers: shared hit=1596687 read=86875 dirtied=1
  • I/O Timings: read=765.913
5. 12,828.282 39,840.261 ↑ 11.4 28,085 1

Hash Join (cost=185,495.28..2,937,029.38 rows=320,950 width=4) (actual time=188.925..39,840.261 rows=28,085 loops=1)

  • Output: serviceren1_.customer_id
  • Hash Cond: (serviceren1_.service_id = service2_.id)
  • Buffers: shared hit=1596687 read=86875 dirtied=1
  • I/O Timings: read=765.913
6. 26,825.431 26,825.431 ↓ 1.0 68,904,899 1

Seq Scan on public.sr_srv_rendered serviceren1_ (cost=0.00..2,501,035.67 rows=65,943,714 width=8) (actual time=0.015..26,825.431 rows=68,904,899 loops=1)

  • Output: serviceren1_.id, serviceren1_.bdate, serviceren1_.comment, serviceren1_.cost, serviceren1_.duration, serviceren1_.edate, serviceren1_.is_rendered, serviceren1_.quantity, serviceren1_.total_cost, serviceren1_.contract_id, serviceren1_.customer_id, serviceren1_.funding_id, serviceren1_.res_group_id, serviceren1_.service_id, serviceren1_.duration_measure_unit_id, serviceren1_.begin_time, serviceren1_.prototype_id, serviceren1_.org_id, serviceren1_.price_list_id, serviceren1_.cul, serviceren1_.parent_id, serviceren1_.payment_status_id, serviceren1_.root_service_id, serviceren1_.is_wholly_rendered, serviceren1_.planned_date, serviceren1_.planned_time, serviceren1_.tooth_number, serviceren1_.is_refused, serviceren1_.aud_who, serviceren1_.aud_when, serviceren1_.aud_source, serviceren1_.aud_who_create, serviceren1_.aud_when_create, serviceren1_.aud_source_create, serviceren1_.is_amalgam_filling, serviceren1_.is_mobile_medical_teams, serviceren1_.close_date, serviceren1_.complex_srv_id, serviceren1_.for_upload, serviceren1_.uid, serviceren1_.is_need_consulting
  • Filter: serviceren1_.is_rendered
  • Rows Removed by Filter: 21520757
  • Buffers: shared hit=1552264 read=86875 dirtied=1
  • I/O Timings: read=765.913
7. 0.023 186.548 ↑ 2,065.6 5 1

Hash (cost=185,366.18..185,366.18 rows=10,328 width=4) (actual time=186.548..186.548 rows=5 loops=1)

  • Output: service2_.id
  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
  • Buffers: shared hit=44423
8. 39.511 186.525 ↑ 2,065.6 5 1

Bitmap Heap Scan on public.sr_service service2_ (cost=541.92..185,366.18 rows=10,328 width=4) (actual time=37.718..186.525 rows=5 loops=1)

  • Output: service2_.id
  • Recheck Cond: (service2_.org_id = 10960)
  • Filter: ((SubPlan 1) OR (service2_.prototype_id = 787))
  • Rows Removed by Filter: 18011
  • Heap Blocks: exact=8304
  • Buffers: shared hit=44423
9. 2.886 2.886 ↑ 1.1 18,016 1

Bitmap Index Scan on sr_service_org_idx (cost=0.00..539.33 rows=20,654 width=0) (actual time=2.886..2.886 rows=18,016 loops=1)

  • Index Cond: (service2_.org_id = 10960)
  • Buffers: shared hit=84
10.          

SubPlan (forBitmap Heap Scan)

11. 17.992 144.128 ↓ 0.0 0 18,016

Nested Loop (cost=0.29..6.80 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=18,016)

  • Buffers: shared hit=36035
12. 18.016 126.112 ↓ 0.0 0 18,016

Nested Loop (cost=0.00..2.48 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=18,016)

  • Output: servicepro4_.srv_prototype_id
  • Buffers: shared hit=36032
13. 54.048 54.048 ↑ 1.0 1 18,016

Seq Scan on public.pci_control_parameter controlpar3_ (cost=0.00..1.25 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=18,016)

  • Output: controlpar3_.id, controlpar3_.is_query, controlpar3_.is_available, controlpar3_.code, controlpar3_.name, controlpar3_.query_text, controlpar3_.service_parameter, controlpar3_.clinic_id, controlpar3_.aud_who, controlpar3_.aud_when, controlpar3_.aud_source, controlpar3_.aud_who_create, controlpar3_.aud_when_create, controlpar3_.aud_source_create
  • Filter: (controlpar3_.id = 16)
  • Rows Removed by Filter: 19
  • Buffers: shared hit=18016
14. 54.048 54.048 ↓ 0.0 0 18,016

Seq Scan on public.pci_control_param_service servicepro4_ (cost=0.00..1.23 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=18,016)

  • Output: servicepro4_.control_parameter_id, servicepro4_.srv_prototype_id, servicepro4_.aud_who, servicepro4_.aud_when, servicepro4_.aud_source, servicepro4_.aud_who_create, servicepro4_.aud_when_create, servicepro4_.aud_source_create
  • Filter: ((servicepro4_.control_parameter_id = 16) AND (servicepro4_.srv_prototype_id = service2_.prototype_id))
  • Rows Removed by Filter: 15
  • Buffers: shared hit=18016
15. 0.024 0.024 ↑ 1.0 1 1

Index Only Scan using sr_srv_prototype_pkey on public.sr_srv_prototype servicepro5_ (cost=0.29..4.30 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)

  • Output: servicepro5_.id
  • Index Cond: (servicepro5_.id = service2_.prototype_id)
  • Heap Fetches: 0
  • Buffers: shared hit=3
Planning time : 1.627 ms
Execution time : 40,534.250 ms