explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SAMc

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,753.067 ↑ 1.0 20 1

Limit (cost=128,604.34..128,605.84 rows=20 width=274) (actual time=1,753.048..1,753.067 rows=20 loops=1)

2. 0.010 1,753.056 ↑ 490.7 20 1

Unique (cost=128,604.34..129,242.25 rows=9,814 width=274) (actual time=1,753.046..1,753.056 rows=20 loops=1)

3. 166.966 1,753.046 ↑ 490.7 20 1

Sort (cost=128,604.34..128,628.88 rows=9,814 width=274) (actual time=1,753.044..1,753.046 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. 351.819 1,586.080 ↓ 3.8 37,536 1

Hash Left Join (cost=96,026.03..127,953.64 rows=9,814 width=274) (actual time=787.940..1,586.080 rows=37,536 loops=1)

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

Hash Left Join (cost=96,021.04..125,468.56 rows=9,814 width=227) (actual time=787.240..1,234.224 rows=37,536 loops=1)

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

Hash Left Join (cost=95,987.87..125,409.49 rows=9,814 width=212) (actual time=787.017..1,222.901 rows=37,536 loops=1)

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

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

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

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

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

Sort (cost=35,863.07..36,084.53 rows=88,583 width=17) (actual time=454.960..469.778 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. 52.960 243.187 ↑ 1.0 87,107 1

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

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

12. 61.062 179.995 ↓ 1.0 428,438 1

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

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

14. 13.421 680.321 ↓ 3.8 37,534 1

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

15. 12.325 666.900 ↓ 3.0 29,445 1

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

16. 28.767 477.905 ↓ 3.0 29,445 1

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

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

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

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

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

  • Hash Cond: ((spim.visit_id)::text = (pr.patient_id)::text)
19. 10.940 10.940 ↑ 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.015..10.940 rows=101,315 loops=1)

20. 21.237 201.197 ↑ 1.0 96,016 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3710kB
21. 173.722 179.960 ↑ 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.090..179.960 rows=96,016 loops=1)

  • Recheck Cond: (center_id = 2)
  • Heap Blocks: exact=39024
22. 6.238 6.238 ↑ 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.238..6.238 rows=96,076 loops=1)

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

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

24. 32.761 108.990 ↓ 1.1 70,103 1

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

  • Group Key: store_patient_indent_details.patient_indent_no
25. 76.229 76.229 ↑ 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.029..76.229 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.225 7.358 ↓ 1.0 28,983 1

Hash (cost=696.51..696.51 rows=28,951 width=20) (actual time=7.358..7.358 rows=28,983 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1728kB
28. 4.133 4.133 ↓ 1.0 28,983 1

Seq Scan on admission adm (cost=0.00..696.51 rows=28,951 width=20) (actual time=0.007..4.133 rows=28,983 loops=1)

29. 0.103 0.215 ↑ 1.0 763 1

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

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

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

31. 0.020 0.037 ↑ 1.0 133 1

Hash (cost=3.33..3.33 rows=133 width=25) (actual time=0.037..0.037 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.397 ms
Execution time : 1,755.599 ms