explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qgqp : Optimization for: Optimization for: plan #LvZ; plan #xWnX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 64.570 ↑ 41.3 3 1

ubquery Scan on object (cost=10,489.23..10,524.26 rows=124 width=153) (actual time=64.548..64.570 rows=3 loops=1)

  • Planning time: 40.358 ms
  • Execution time: 64.844 ms
2. 0.191 64.569 ↑ 41.3 3 1

HashAggregate (cost=10,489.23..10,523.02 rows=124 width=81) (actual time=64.547..64.569 rows=3 loops=1)

  • Group Key: patient_registration.patient_registration_accountno, patient_registration.patient_registration_dob, patient_registration.pat
  • Filter: (string_agg((problem_list.problem_list_dx_code)::text, ','::text) ~~* '%,%'::text)
  • Rows Removed by Filter: 42
3. 0.158 64.378 ↑ 2.6 48 1

Nested Loop Left Join (cost=940.64..10,486.44 rows=124 width=81) (actual time=6.152..64.378 rows=48 loops=1)

  • -> Index Scan using emp_profile_pkey on emp_profile (cost=0.27..0.69 rows=1 width=19) (actual time=0.002..0.003 rows=1 loops=48
4. 0.160 64.220 ↑ 2.6 48 1

Nested Loop (cost=940.37..10,399.90 rows=124 width=74) (actual time=6.142..64.220 rows=48 loops=1)

  • -> Index Scan using ins_company_pkey on ins_company a (cost=0.29..0.34 rows=1 width=27) (actual time=0.003..0.003 rows=1
  • Index Cond: (emp_profile_empid = patient_registration.patient_registration_principal_doctor)
5. 0.039 64.060 ↑ 3.6 48 1

Hash Join (cost=940.08..10,338.59 rows=175 width=59) (actual time=6.132..64.060 rows=48 loops=1)

  • Hash Cond: (ins_comp_addr.ins_comp_addr_inscompany_id = b.ins_company_id)
  • Index Cond: (ins_company_id = ins_comp_addr.ins_comp_addr_inscompany_id)
6. 0.178 63.559 ↑ 28.1 48 1

Nested Loop (cost=901.02..10,292.73 rows=1,348 width=55) (actual time=5.656..63.559 rows=48 loops=1)

  • -> Index Scan using ins_comp_addr_id_idx on ins_comp_addr (cost=0.29..0.40 rows=1 width=8) (actual time=0.003
7. 0.440 63.381 ↑ 28.1 48 1

Nested Loop (cost=900.74..9,738.08 rows=1,348 width=55) (actual time=5.645..63.381 rows=48 loops=1)

  • -> Index Scan using patient_ins_detail_patientid_idx on patient_ins_detail (cost=0.29..1.62 rows=3 widt
  • Index Cond: (ins_comp_addr_id = patient_ins_detail.patient_ins_detail_insaddressid)
8. 57.880 62.941 ↑ 9.1 45 1

Hash Join (cost=900.45..9,065.07 rows=408 width=55) (actual time=5.621..62.941 rows=45 loops=1)

  • Hash Cond: (problem_list.problem_list_patient_id = patient_registration.patient_registration_id)
  • -> Seq Scan on problem_list (cost=0.00..8157.48 rows=815 width=10) (actual time=0.531..57.815 row
  • Filter: (problem_list_isactive AND ((problem_list_dx_code)::text = ANY ('{140.0,140.1,140.9,1
  • Rows Removed by Filter: 2394
  • Index Cond: (patient_ins_detail_patientid = patient_registration.patient_registration_id)
  • Filter: patient_ins_detail_isactive
  • Rows Removed by Filter: 1
9. 5.061 5.061 ↓ 2.0 4,572 1

Hash (cost=871.92..871.92 rows=2,282 width=45) (actual time=5.061..5.061 rows=4,572 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 436kB
  • -> Seq Scan on patient_registration (cost=14.88..871.92 rows=2282 width=45) (actual time=0.
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 11
  • -> Seq Scan on ccm_patient_log (cost=0.00..13.90 rows=390 width=4) (actual time=0.0
10. 0.462 0.462 ↓ 1.0 1,196 1

Hash (cost=24.58..24.58 rows=1,158 width=4) (actual time=0.462..0.462 rows=1,196 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 59kB
  • -> Seq Scan on ins_company_ch_mapping b (cost=0.00..24.58 rows=1158 width=4) (actual time=0.008..0.248 rows=1