explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jsVw : Optimization for: plan #Z4ab

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 29.269 ↓ 0.0 0 1

Nested Loop (cost=41,610,961.17..41,610,969.25 rows=1 width=4) (actual time=29.269..29.269 rows=0 loops=1)

2.          

CTE t

3. 0.003 29.266 ↓ 0.0 0 1

HashAggregate (cost=41,610,952.14..41,610,952.15 rows=1 width=8) (actual time=29.266..29.266 rows=0 loops=1)

  • Group Key: f.step_id, array_agg(DISTINCT s_1.id)
4. 0.002 29.263 ↓ 0.0 0 1

GroupAggregate (cost=41,610,952.11..41,610,952.13 rows=1 width=8) (actual time=29.263..29.263 rows=0 loops=1)

  • Group Key: f.step_id
5. 0.034 29.261 ↓ 0.0 0 1

Sort (cost=41,610,952.11..41,610,952.11 rows=1 width=8) (actual time=29.261..29.261 rows=0 loops=1)

  • Sort Key: f.step_id
  • Sort Method: quicksort Memory: 25kB
6. 0.001 29.227 ↓ 0.0 0 1

Nested Loop (cost=41,359,898.53..41,610,952.10 rows=1 width=8) (actual time=29.227..29.227 rows=0 loops=1)

  • Join Filter: ((f.speciality_code_arr[1])::text = (sp.code)::text)
7. 0.001 29.226 ↓ 0.0 0 1

Hash Join (cost=41,359,897.11..41,610,934.42 rows=1 width=55) (actual time=29.226..29.226 rows=0 loops=1)

  • Hash Cond: ((c2.clinic_id = c.clinic_id) AND (c2.patient_id = c.patient_id) AND (f.step_admission_date = s_1.admission_date))
  • Join Filter: (f.step_id <> s_1.id)
8. 0.001 29.225 ↓ 0.0 0 1

Nested Loop (cost=1.00..56,992.18 rows=5,277 width=63) (actual time=29.225..29.225 rows=0 loops=1)

9. 29.224 29.224 ↓ 0.0 0 1

Index Scan using fin_bill_steps_bill_id_step_id_idx on fin_bill_steps f (cost=0.43..11,583.59 rows=5,277 width=55) (actual time=29.224..29.224 rows=0 loops=1)

  • Index Cond: (bill_id = 1943)
  • Filter: ((speciality_code_arr[1])::text <> ALL ('{14,140102,140103,140104,140105,140106,140107,1401,2005,2027,2004}'::text[]))
  • Rows Removed by Filter: 15999
10. 0.000 0.000 ↓ 0.0 0

Subquery Scan on c2 (cost=0.56..8.59 rows=1 width=12) (never executed)

  • Filter: (c2.case_type_id = 1)
11. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.56..8.58 rows=1 width=12) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_pk on mc_case (cost=0.56..8.58 rows=1 width=12) (never executed)

  • Index Cond: (id = f.case_id)
13. 0.000 0.000 ↓ 0.0 0

Hash (cost=41,250,704.10..41,250,704.10 rows=4,674,401 width=24) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=101,282.02..41,250,704.10 rows=4,674,401 width=24) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=101,281.45..1,027,483.49 rows=4,674,401 width=16) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using fin_bill_main_pkey on fin_bill_main (cost=0.28..8.29 rows=1 width=8) (never executed)

  • Index Cond: (id = 1973)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on mc_step s_1 (cost=101,281.18..980,731.19 rows=4,674,401 width=16) (never executed)

  • Recheck Cond: ((admission_date >= fin_bill_main.from_date) AND (admission_date <= fin_bill_main.to_date))
18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mc_step_admission_date_outcome_date_idx (cost=0.00..100,112.58 rows=4,674,401 width=0) (never executed)

  • Index Cond: ((admission_date >= fin_bill_main.from_date) AND (admission_date <= fin_bill_main.to_date))
19. 0.000 0.000 ↓ 0.0 0

Subquery Scan on c (cost=0.56..8.59 rows=1 width=12) (never executed)

  • Filter: (c.case_type_id = 1)
20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.56..8.58 rows=1 width=12) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_pk on mc_case mc_case_1 (cost=0.56..8.58 rows=1 width=12) (never executed)

  • Index Cond: (id = s_1.case_id)
22. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.42..17.65 rows=1 width=5) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.42..17.65 rows=1 width=5) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.15..17.35 rows=1 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.86..16.90 rows=1 width=4) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_res_group_pk on sr_res_group g (cost=0.57..8.59 rows=1 width=4) (never executed)

  • Index Cond: (id = s_1.res_group_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_position_pk on pim_employee_position p (cost=0.29..8.31 rows=1 width=12) (never executed)

  • Index Cond: (id = g.responsible_id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_position_pk on pim_position t_1 (cost=0.29..0.44 rows=1 width=8) (never executed)

  • Index Cond: (id = p.position_id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_speciality_pk on pim_speciality sp (cost=0.27..0.29 rows=1 width=9) (never executed)

  • Index Cond: (id = t_1.speciality_id)
30. 0.000 29.268 ↓ 0.0 0 1

Nested Loop (cost=0.43..8.48 rows=1 width=36) (actual time=29.268..29.268 rows=0 loops=1)

31. 29.268 29.268 ↓ 0.0 0 1

CTE Scan on t (cost=0.00..0.02 rows=1 width=36) (actual time=29.268..29.268 rows=0 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using fin_bill_steps_bill_id_step_id_idx on fin_bill_steps s (cost=0.43..8.45 rows=1 width=8) (never executed)

  • Index Cond: ((bill_id = 1943) AND (step_id = t.step_id))
33. 0.000 0.000 ↓ 0.0 0

Limit (cost=8.59..8.60 rows=1 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=8.59..8.60 rows=1 width=8) (never executed)

  • Group Key: ss.case_id, ss.id
35. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_step_pk on mc_step ss (cost=0.56..8.58 rows=1 width=8) (never executed)

  • Index Cond: (id = (t.steps_arr)[1])
Planning time : 9.109 ms
Execution time : 29.657 ms