explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7bfq

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 464.626 ↓ 0.0 0 1

Limit (cost=57,521.00..57,521.68 rows=9 width=274) (actual time=464.626..464.626 rows=0 loops=1)

2. 0.000 464.624 ↓ 0.0 0 1

Unique (cost=57,521.00..57,521.59 rows=9 width=274) (actual time=464.624..464.624 rows=0 loops=1)

3. 0.041 464.624 ↓ 0.0 0 1

Sort (cost=57,521.00..57,521.02 rows=9 width=274) (actual time=464.624..464.624 rows=0 loops=1)

  • Sort Key: spim.patient_indent_no, spim.indent_type, spim.visit_id, spim.indent_store, spim.expected_date, spim.status, spim.dispense_status, spim.remarks, spim.username, spim.mod_time, spim.process_type, spim.priority, spim.finalized_date, spim.finalized_user, spim.prescribing_doctor_name, spim.exclude_from_vat, spim.newly_created_indent, (COALESCE(ip.priority, 'R'::character varying)), pd.mr_no, (get_patient_name(pd.salutation, pd.patient_name, pd.middle_name, pd.last_name)), wn.ward_name, wn.ward_no, bn.bed_name, (CASE WHEN ((foo.salecount > 0) AND (foo.issuecount > 0)) THEN 'Sold/Issued'::text WHEN ((foo.salecount > 0) AND (foo.issuecount <= 0)) THEN 'Sold'::text WHEN ((foo.salecount <= 0) AND (foo.issuecount > 0)) THEN 'Issued'::text ELSE NULL::text END), ip.verbal_order
  • Sort Method: quicksort Memory: 25kB
4. 0.003 464.583 ↓ 0.0 0 1

Merge Right Join (cost=39,515.27..57,520.86 rows=9 width=274) (actual time=464.583..464.583 rows=0 loops=1)

  • Merge Cond: ((sipd.patient_indent_no)::text = (spim.patient_indent_no)::text)
5. 0.007 450.074 ↑ 88,583.0 1 1

GroupAggregate (cost=35,863.07..38,077.65 rows=88,583 width=90) (actual time=450.074..450.074 rows=1 loops=1)

  • Group Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
6. 211.218 450.067 ↑ 44,291.5 2 1

Sort (cost=35,863.07..36,084.53 rows=88,583 width=17) (actual time=450.067..450.067 rows=2 loops=1)

  • Sort Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
  • Sort Method: external merge Disk: 2656kB
7. 50.877 238.849 ↑ 1.0 87,107 1

Hash Join (cost=22,193.52..26,763.88 rows=88,583 width=17) (actual time=179.620..238.849 rows=87,107 loops=1)

  • Hash Cond: (sipd.patient_presc_id = ip.prescription_id)
8. 8.643 8.643 ↑ 1.0 88,583 1

Seq Scan on store_indent_prescription_details sipd (cost=0.00..1,623.83 rows=88,583 width=19) (actual time=0.023..8.643 rows=88,583 loops=1)

9. 60.389 179.329 ↓ 1.0 428,438 1

Hash (cost=15,164.23..15,164.23 rows=428,423 width=6) (actual time=179.329..179.329 rows=428,438 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3125kB
10. 118.940 118.940 ↓ 1.0 428,438 1

Seq Scan on ip_prescription ip (cost=0.00..15,164.23 rows=428,423 width=6) (actual time=0.098..118.940 rows=428,438 loops=1)

11. 0.001 14.506 ↓ 0.0 0 1

Materialize (cost=3,652.20..18,333.57 rows=9 width=241) (actual time=14.506..14.506 rows=0 loops=1)

12. 0.000 14.505 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,652.20..18,333.55 rows=9 width=241) (actual time=14.505..14.505 rows=0 loops=1)

13. 0.001 14.505 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,652.05..18,331.90 rows=9 width=225) (actual time=14.505..14.505 rows=0 loops=1)

14. 0.000 14.504 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,651.78..18,329.24 rows=9 width=210) (actual time=14.504..14.504 rows=0 loops=1)

15. 0.000 14.504 ↓ 0.0 0 1

Nested Loop (cost=3,651.49..18,267.69 rows=9 width=206) (actual time=14.504..14.504 rows=0 loops=1)

16. 0.001 14.504 ↓ 0.0 0 1

Nested Loop (cost=3,651.07..18,256.70 rows=9 width=174) (actual time=14.504..14.504 rows=0 loops=1)

17. 0.001 14.503 ↓ 0.0 0 1

Merge Left Join (cost=3,650.64..17,500.65 rows=90 width=155) (actual time=14.503..14.503 rows=0 loops=1)

  • Merge Cond: ((spim.patient_indent_no)::text = (foo.patient_indent_no)::text)
18. 0.008 14.502 ↓ 0.0 0 1

Sort (cost=3,650.22..3,650.45 rows=90 width=123) (actual time=14.502..14.502 rows=0 loops=1)

  • Sort Key: spim.patient_indent_no
  • Sort Method: quicksort Memory: 25kB
19. 14.494 14.494 ↓ 0.0 0 1

Seq Scan on store_patient_indent_main spim (cost=0.00..3,647.30 rows=90 width=123) (actual time=14.494..14.494 rows=0 loops=1)

  • Filter: ((indent_type = ANY ('{I,D}'::bpchar[])) AND (dispense_status = ANY ('{O,P}'::bpchar[])) AND ((expected_date)::date >= '2019-11-25'::date))
  • Rows Removed by Filter: 101315
20. 0.000 0.000 ↓ 0.0 0

Subquery Scan on foo (cost=0.42..13,691.48 rows=63,178 width=41) (never executed)

21. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.42..12,112.03 rows=63,178 width=25) (never executed)

  • Group Key: store_patient_indent_details.patient_indent_no
22. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_indent_no_idx on store_patient_indent_details (cost=0.42..10,445.85 rows=137,920 width=17) (never executed)

  • Filter: ((sale_item_id IS NOT NULL) OR (item_issue_no IS NOT NULL))
23. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..8.40 rows=1 width=35) (never executed)

  • Index Cond: ((patient_id)::text = (spim.visit_id)::text)
  • Filter: (center_id = 2)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..1.22 rows=1 width=47) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using admission_pkey on admission adm (cost=0.29..6.84 rows=1 width=20) (never executed)

  • Index Cond: ((patient_id)::text = (spim.visit_id)::text)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using bed_names_pkey on bed_names bn (cost=0.28..0.30 rows=1 width=23) (never executed)

  • Index Cond: (bed_id = adm.bed_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using ward_names_pkey on ward_names wn (cost=0.14..0.18 rows=1 width=25) (never executed)

  • Index Cond: ((ward_no)::text = (bn.ward_no)::text)
Planning time : 9.767 ms
Execution time : 465.627 ms