explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1oq

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 30,843.951 ↑ 1.0 20 1

Limit (cost=1,679,754.55..1,679,756.40 rows=20 width=269) (actual time=30,843.896..30,843.951 rows=20 loops=1)

2. 0.019 30,843.913 ↑ 6,497.2 20 1

Unique (cost=1,679,754.55..1,687,876.05 rows=129,944 width=269) (actual time=30,843.893..30,843.913 rows=20 loops=1)

3. 3,492.835 30,843.894 ↑ 6,497.2 20 1

Sort (cost=1,679,754.55..1,680,079.41 rows=129,944 width=269) (actual time=30,843.888..30,843.894 rows=20 loops=1)

  • Sort Key: spim.patient_indent_no, spim.indent_type, spim.visit_id, 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 merge Disk: 124608kB
4. 7,895.281 27,351.059 ↓ 5.0 653,104 1

Merge Right Join (cost=1,227,753.03..1,668,717.42 rows=129,944 width=269) (actual time=13,059.114..27,351.059 rows=653,104 loops=1)

  • Merge Cond: ((sipd.patient_indent_no)::text = (spim.patient_indent_no)::text)
5. 525.428 9,601.855 ↑ 1.0 1,638,921 1

GroupAggregate (cost=651,594.94..728,986.71 rows=1,719,817 width=90) (actual time=8,538.783..9,601.855 rows=1,638,921 loops=1)

  • Group Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
6. 5,049.297 9,076.427 ↑ 1.0 1,703,473 1

Sort (cost=651,594.94..655,894.49 rows=1,719,817 width=17) (actual time=8,538.771..9,076.427 rows=1,703,473 loops=1)

  • Sort Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
  • Sort Method: external merge Disk: 51528kB
7. 983.268 4,027.130 ↑ 1.0 1,703,481 1

Merge Join (cost=16,137.78..448,280.01 rows=1,719,817 width=17) (actual time=84.820..4,027.130 rows=1,703,481 loops=1)

  • Merge Cond: (sipd.patient_presc_id = ip.prescription_id)
8. 832.629 832.629 ↓ 1.0 1,721,329 1

Index Scan using idx_store_indent_prescription_details_patient_presc_id on store_indent_prescription_details sipd (cost=0.43..85,162.17 rows=1,719,817 width=19) (actual time=0.033..832.629 rows=1,721,329 loops=1)

9. 2,211.233 2,211.233 ↓ 1.0 4,435,533 1

Index Scan using ip_prescription_pkey on ip_prescription ip (cost=0.43..296,394.72 rows=4,432,357 width=6) (actual time=0.032..2,211.233 rows=4,435,533 loops=1)

10. 190.206 9,853.923 ↓ 9.2 653,104 1

Materialize (cost=576,158.08..846,980.34 rows=71,090 width=237) (actual time=4,519.114..9,853.923 rows=653,104 loops=1)

11. 103.637 9,663.717 ↓ 4.2 297,495 1

Nested Loop (cost=576,158.08..846,802.61 rows=71,090 width=237) (actual time=4,519.105..9,663.717 rows=297,495 loops=1)

12. 383.888 7,180.120 ↓ 4.2 297,495 1

Merge Left Join (cost=576,157.65..804,976.53 rows=71,090 width=204) (actual time=4,519.064..7,180.120 rows=297,495 loops=1)

  • Merge Cond: ((spim.patient_indent_no)::text = (foo.patient_indent_no)::text)
13. 1,088.501 4,659.824 ↓ 4.2 297,495 1

Sort (cost=576,157.22..576,334.95 rows=71,090 width=172) (actual time=4,495.179..4,659.824 rows=297,495 loops=1)

  • Sort Key: spim.patient_indent_no
  • Sort Method: external merge Disk: 46144kB
14. 66.049 3,571.323 ↓ 4.2 297,495 1

Hash Left Join (cost=499,241.88..570,428.31 rows=71,090 width=172) (actual time=2,918.859..3,571.323 rows=297,495 loops=1)

  • Hash Cond: ((bn.ward_no)::text = (wn.ward_no)::text)
15. 68.216 3,505.201 ↓ 4.2 297,495 1

Hash Left Join (cost=499,227.32..570,216.11 rows=71,090 width=156) (actual time=2,918.764..3,505.201 rows=297,495 loops=1)

  • Hash Cond: (adm.bed_id = bn.bed_id)
16. 90.358 3,436.381 ↓ 4.2 297,495 1

Hash Left Join (cost=499,103.51..569,904.23 rows=71,090 width=140) (actual time=2,918.127..3,436.381 rows=297,495 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (adm.patient_id)::text)
17. 292.626 3,265.663 ↓ 4.2 297,495 1

Hash Join (cost=483,742.13..554,356.24 rows=71,090 width=136) (actual time=2,836.732..3,265.663 rows=297,495 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (pr.patient_id)::text)
18. 140.684 140.684 ↓ 1.0 297,499 1

Seq Scan on store_patient_indent_main spim (cost=0.00..46,035.63 rows=293,895 width=117) (actual time=0.038..140.684 rows=297,499 loops=1)

  • Filter: (indent_store = 210)
  • Rows Removed by Filter: 640937
19. 419.267 2,832.353 ↑ 1.0 1,719,085 1

Hash (cost=414,223.63..414,223.63 rows=1,724,477 width=35) (actual time=2,832.352..2,832.353 rows=1,719,085 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 65214kB
20. 2,272.448 2,413.086 ↑ 1.0 1,719,085 1

Bitmap Heap Scan on patient_registration pr (cost=33,689.13..414,223.63 rows=1,724,477 width=35) (actual time=206.017..2,413.086 rows=1,719,085 loops=1)

  • Recheck Cond: (center_id = 9)
  • Heap Blocks: exact=315773
21. 140.638 140.638 ↓ 1.0 1,734,780 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..33,258.01 rows=1,724,477 width=0) (actual time=140.638..140.638 rows=1,734,780 loops=1)

  • Index Cond: (center_id = 9)
22. 41.322 80.360 ↓ 1.0 199,921 1

Hash (cost=8,870.02..8,870.02 rows=199,734 width=19) (actual time=80.360..80.360 rows=199,921 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 12201kB
23. 39.038 39.038 ↓ 1.0 199,921 1

Seq Scan on admission adm (cost=0.00..8,870.02 rows=199,734 width=19) (actual time=0.019..39.038 rows=199,921 loops=1)

24. 0.255 0.604 ↑ 1.0 1,501 1

Hash (cost=75.03..75.03 rows=1,501 width=24) (actual time=0.603..0.604 rows=1,501 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 102kB
25. 0.349 0.349 ↑ 1.0 1,501 1

Seq Scan on bed_names bn (cost=0.00..75.03 rows=1,501 width=24) (actual time=0.013..0.349 rows=1,501 loops=1)

26. 0.040 0.073 ↑ 1.0 201 1

Hash (cost=8.03..8.03 rows=201 width=25) (actual time=0.073..0.073 rows=201 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
27. 0.033 0.033 ↑ 1.0 201 1

Seq Scan on ward_names wn (cost=0.00..8.03 rows=201 width=25) (actual time=0.008..0.033 rows=201 loops=1)

28. 129.923 2,136.408 ↓ 1.7 860,140 1

Subquery Scan on foo (cost=0.43..226,001.53 rows=516,568 width=41) (actual time=0.926..2,136.408 rows=860,140 loops=1)

29. 447.940 2,006.485 ↓ 1.7 860,140 1

GroupAggregate (cost=0.43..202,755.97 rows=516,568 width=25) (actual time=0.920..2,006.485 rows=860,140 loops=1)

  • Group Key: store_patient_indent_details.patient_indent_no
30. 1,558.545 1,558.545 ↓ 1.0 1,951,839 1

Index Scan using patient_indent_no_idx on store_patient_indent_details (cost=0.43..172,656.06 rows=1,947,050 width=17) (actual time=0.908..1,558.545 rows=1,951,839 loops=1)

  • Filter: ((sale_item_id IS NOT NULL) OR (item_issue_no IS NOT NULL))
  • Rows Removed by Filter: 1107431
31. 2,379.960 2,379.960 ↑ 1.0 1 297,495

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.59 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=297,495)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
Planning time : 7.642 ms
Execution time : 30,881.733 ms