explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jp1Z

Settings
# exclusive inclusive rows x rows loops node
1. 291.559 39,559.610 ↑ 21.6 14,828 1

Hash Join (cost=2,944,875.18..2,988,049.01 rows=320,948 width=65) (actual time=38,995.121..39,559.610 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=1663191 read=4348
  • I/O Timings: read=46.003
2. 274.489 274.489 ↓ 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.013..274.489 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.535 38,993.562 ↑ 21.1 14,828 1

Hash (cost=2,940,955.63..2,940,955.63 rows=313,564 width=4) (actual time=38,993.562..38,993.562 rows=14,828 loops=1)

  • Output: serviceren1_.customer_id
  • Buckets: 524288 Batches: 1 Memory Usage: 4618kB
  • Buffers: shared hit=1643180 read=4348
  • I/O Timings: read=46.003
4. 107.866 38,988.027 ↑ 21.1 14,828 1

HashAggregate (cost=2,937,819.99..2,940,955.63 rows=313,564 width=4) (actual time=38,981.186..38,988.027 rows=14,828 loops=1)

  • Output: serviceren1_.customer_id
  • Group Key: serviceren1_.customer_id
  • Buffers: shared hit=1643180 read=4348
  • I/O Timings: read=46.003
5. 13,007.453 38,880.161 ↑ 11.4 28,085 1

Hash Join (cost=185,495.28..2,937,017.62 rows=320,948 width=4) (actual time=32.767..38,880.161 rows=28,085 loops=1)

  • Output: serviceren1_.customer_id
  • Hash Cond: (serviceren1_.service_id = service2_.id)
  • Buffers: shared hit=1643180 read=4348
  • I/O Timings: read=46.003
6. 25,842.124 25,842.124 ↓ 1.0 68,904,739 1

Seq Scan on public.sr_srv_rendered serviceren1_ (cost=0.00..2,501,024.99 rows=65,943,433 width=8) (actual time=0.014..25,842.124 rows=68,904,739 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: 21520722
  • Buffers: shared hit=1634784 read=4348
  • I/O Timings: read=46.003
7. 0.014 30.584 ↑ 2,065.6 5 1

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

  • Output: service2_.id
  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
  • Buffers: shared hit=8396
8. 27.749 30.570 ↑ 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=10.948..30.570 rows=5 loops=1)

  • Output: service2_.id
  • Recheck Cond: (service2_.org_id = 10960)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (service2_.prototype_id = 787))
  • Rows Removed by Filter: 18011
  • Heap Blocks: exact=8304
  • Buffers: shared hit=8396
9. 2.768 2.768 ↑ 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.768..2.768 rows=18,016 loops=1)

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

SubPlan (forBitmap Heap Scan)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..6.80 rows=1 width=0) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.48 rows=1 width=4) (never executed)

  • Output: servicepro4_.srv_prototype_id
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pci_control_parameter controlpar3_ (cost=0.00..1.25 rows=1 width=4) (never executed)

  • 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)
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pci_control_param_service servicepro4_ (cost=0.00..1.23 rows=1 width=8) (never executed)

  • 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))
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sr_srv_prototype_pkey on public.sr_srv_prototype servicepro5_ (cost=0.29..4.30 rows=1 width=4) (never executed)

  • Output: servicepro5_.id
  • Index Cond: (servicepro5_.id = service2_.prototype_id)
  • Heap Fetches: 0
16. 0.006 0.053 ↑ 1.0 2 1

Nested Loop (cost=0.29..11.08 rows=2 width=4) (actual time=0.043..0.053 rows=2 loops=1)

  • Output: servicepro5__1.id
  • Buffers: shared hit=8
17. 0.003 0.027 ↑ 1.0 2 1

Nested Loop (cost=0.00..2.46 rows=2 width=4) (actual time=0.025..0.027 rows=2 loops=1)

  • Output: servicepro4__1.srv_prototype_id
  • Buffers: shared hit=2
18. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on public.pci_control_parameter controlpar3__1 (cost=0.00..1.25 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Output: controlpar3__1.id, controlpar3__1.is_query, controlpar3__1.is_available, controlpar3__1.code, controlpar3__1.name, controlpar3__1.query_text, controlpar3__1.service_parameter, controlpar3__1.clinic_id, controlpar3__1.aud_who, controlpar3__1.aud_when, controlpar3__1.aud_source, controlpar3__1.aud_who_create, controlpar3__1.aud_when_create, controlpar3__1.aud_source_create
  • Filter: (controlpar3__1.id = 16)
  • Rows Removed by Filter: 19
  • Buffers: shared hit=1
19. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on public.pci_control_param_service servicepro4__1 (cost=0.00..1.19 rows=2 width=8) (actual time=0.008..0.009 rows=2 loops=1)

  • Output: servicepro4__1.control_parameter_id, servicepro4__1.srv_prototype_id, servicepro4__1.aud_who, servicepro4__1.aud_when, servicepro4__1.aud_source, servicepro4__1.aud_who_create, servicepro4__1.aud_when_create, servicepro4__1.aud_source_create
  • Filter: (servicepro4__1.control_parameter_id = 16)
  • Rows Removed by Filter: 13
  • Buffers: shared hit=1
20. 0.020 0.020 ↑ 1.0 1 2

Index Only Scan using sr_srv_prototype_pkey on public.sr_srv_prototype servicepro5__1 (cost=0.29..4.30 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=2)

  • Output: servicepro5__1.id
  • Index Cond: (servicepro5__1.id = servicepro4__1.srv_prototype_id)
  • Heap Fetches: 0
  • Buffers: shared hit=6
Planning time : 2.053 ms
Execution time : 39,563.273 ms