explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5VJV

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 1,745.150 ↑ 1.0 20 1

Limit (cost=128,605.06..128,606.56 rows=20 width=274) (actual time=1,745.126..1,745.150 rows=20 loops=1)

2. 0.012 1,745.135 ↑ 490.7 20 1

Unique (cost=128,605.06..129,242.97 rows=9,814 width=274) (actual time=1,745.123..1,745.135 rows=20 loops=1)

3. 167.957 1,745.123 ↑ 490.7 20 1

Sort (cost=128,605.06..128,629.60 rows=9,814 width=274) (actual time=1,745.122..1,745.123 rows=20 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: external sort Disk: 7320kB
4. 344.951 1,577.166 ↓ 3.8 37,536 1

Hash Left Join (cost=96,026.75..127,954.36 rows=9,814 width=274) (actual time=787.590..1,577.166 rows=37,536 loops=1)

  • Hash Cond: ((bn.ward_no)::text = (wn.ward_no)::text)
5. 10.970 1,232.182 ↓ 3.8 37,536 1

Hash Left Join (cost=96,021.76..125,469.28 rows=9,814 width=227) (actual time=787.488..1,232.182 rows=37,536 loops=1)

  • Hash Cond: (adm.bed_id = bn.bed_id)
6. 14.754 1,221.013 ↓ 3.8 37,536 1

Hash Left Join (cost=95,988.59..125,410.21 rows=9,814 width=212) (actual time=787.280..1,221.013 rows=37,536 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (adm.patient_id)::text)
7. 29.256 1,199.019 ↓ 3.8 37,536 1

Merge Right Join (cost=94,929.47..124,325.33 rows=9,814 width=208) (actual time=780.017..1,199.019 rows=37,536 loops=1)

  • Merge Cond: ((sipd.patient_indent_no)::text = (spim.patient_indent_no)::text)
8. 21.967 509.346 ↑ 1.1 83,435 1

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

  • Group Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
9. 244.483 487.379 ↑ 1.0 87,076 1

Sort (cost=35,863.07..36,084.53 rows=88,583 width=17) (actual time=472.415..487.379 rows=87,076 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
10. 54.368 242.896 ↑ 1.0 87,107 1

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

  • Hash Cond: (sipd.patient_presc_id = ip.prescription_id)
11. 9.969 9.969 ↑ 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.044..9.969 rows=88,583 loops=1)

12. 59.456 178.559 ↓ 1.0 428,438 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3125kB
13. 119.103 119.103 ↓ 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.023..119.103 rows=428,438 loops=1)

14. 13.491 660.417 ↓ 3.8 37,534 1

Materialize (cost=59,066.40..85,030.05 rows=9,814 width=206) (actual time=307.591..660.417 rows=37,534 loops=1)

15. 10.795 646.926 ↓ 3.0 29,445 1

Nested Loop (cost=59,066.40..85,005.52 rows=9,814 width=206) (actual time=307.588..646.926 rows=29,445 loops=1)

16. 28.798 459.461 ↓ 3.0 29,445 1

Merge Left Join (cost=59,065.98..73,025.25 rows=9,814 width=174) (actual time=307.560..459.461 rows=29,445 loops=1)

  • Merge Cond: ((spim.patient_indent_no)::text = (foo.patient_indent_no)::text)
17. 82.315 312.545 ↓ 3.0 29,445 1

Sort (cost=59,065.56..59,090.09 rows=9,814 width=142) (actual time=304.109..312.545 rows=29,445 loops=1)

  • Sort Key: spim.patient_indent_no
  • Sort Method: external merge Disk: 3744kB
18. 33.450 230.230 ↓ 3.0 29,445 1

Hash Join (cost=50,997.76..58,414.86 rows=9,814 width=142) (actual time=186.494..230.230 rows=29,445 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (pr.patient_id)::text)
19. 10.542 10.542 ↑ 1.0 101,315 1

Seq Scan on store_patient_indent_main spim (cost=0.00..2,634.15 rows=101,315 width=123) (actual time=0.014..10.542 rows=101,315 loops=1)

20. 21.119 186.238 ↑ 1.0 96,016 1

Hash (cost=49,030.71..49,030.71 rows=96,804 width=35) (actual time=186.238..186.238 rows=96,016 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3710kB
21. 158.213 165.119 ↑ 1.0 96,016 1

Bitmap Heap Scan on patient_registration pr (cost=1,814.66..49,030.71 rows=96,804 width=35) (actual time=11.919..165.119 rows=96,016 loops=1)

  • Recheck Cond: (center_id = 2)
  • Heap Blocks: exact=39024
22. 6.906 6.906 ↑ 1.0 96,076 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..1,790.45 rows=96,804 width=0) (actual time=6.906..6.906 rows=96,076 loops=1)

  • Index Cond: (center_id = 2)
23. 9.422 118.118 ↓ 1.1 70,103 1

Subquery Scan on foo (cost=0.42..13,691.48 rows=63,178 width=41) (actual time=0.053..118.118 rows=70,103 loops=1)

24. 31.561 108.696 ↓ 1.1 70,103 1

GroupAggregate (cost=0.42..12,112.03 rows=63,178 width=25) (actual time=0.048..108.696 rows=70,103 loops=1)

  • Group Key: store_patient_indent_details.patient_indent_no
25. 77.135 77.135 ↑ 1.0 137,148 1

Index Scan using patient_indent_no_idx on store_patient_indent_details (cost=0.42..10,445.85 rows=137,920 width=17) (actual time=0.033..77.135 rows=137,148 loops=1)

  • Filter: ((sale_item_id IS NOT NULL) OR (item_issue_no IS NOT NULL))
  • Rows Removed by Filter: 119056
26. 176.670 176.670 ↑ 1.0 1 29,445

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..1.22 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=29,445)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
27. 3.040 7.240 ↑ 1.0 28,983 1

Hash (cost=696.83..696.83 rows=28,983 width=20) (actual time=7.240..7.240 rows=28,983 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1728kB
28. 4.200 4.200 ↑ 1.0 28,983 1

Seq Scan on admission adm (cost=0.00..696.83 rows=28,983 width=20) (actual time=0.008..4.200 rows=28,983 loops=1)

29. 0.085 0.199 ↑ 1.0 763 1

Hash (cost=23.63..23.63 rows=763 width=23) (actual time=0.199..0.199 rows=763 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
30. 0.114 0.114 ↑ 1.0 763 1

Seq Scan on bed_names bn (cost=0.00..23.63 rows=763 width=23) (actual time=0.007..0.114 rows=763 loops=1)

31. 0.016 0.033 ↑ 1.0 133 1

Hash (cost=3.33..3.33 rows=133 width=25) (actual time=0.033..0.033 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
32. 0.017 0.017 ↑ 1.0 133 1

Seq Scan on ward_names wn (cost=0.00..3.33 rows=133 width=25) (actual time=0.006..0.017 rows=133 loops=1)

Planning time : 10.729 ms
Execution time : 1,747.747 ms