explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T3jW

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 26,627.093 ↑ 1.0 20 1

Limit (cost=1,111,011.87..1,111,013.32 rows=20 width=271) (actual time=26,627.056..26,627.093 rows=20 loops=1)

2. 0.017 26,627.078 ↑ 5,814.8 20 1

Unique (cost=1,111,011.87..1,118,280.37 rows=116,296 width=271) (actual time=26,627.055..26,627.078 rows=20 loops=1)

3. 2,865.883 26,627.061 ↑ 5,814.8 20 1

Sort (cost=1,111,011.87..1,111,302.61 rows=116,296 width=271) (actual time=26,627.052..26,627.061 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: 107624kB
4. 5,504.438 23,761.178 ↓ 4.9 564,911 1

Merge Right Join (cost=959,623.02..1,101,227.05 rows=116,296 width=271) (actual time=13,832.653..23,761.178 rows=564,911 loops=1)

  • Merge Cond: ((sipd.patient_indent_no)::text = (spim.patient_indent_no)::text)
5. 448.570 8,204.058 ↑ 1.1 1,397,406 1

GroupAggregate (cost=440,658.42..477,354.50 rows=1,467,843 width=90) (actual time=7,155.251..8,204.058 rows=1,397,406 loops=1)

  • Group Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
6. 4,374.742 7,755.488 ↑ 1.0 1,452,661 1

Sort (cost=440,658.42..444,328.03 rows=1,467,843 width=17) (actual time=7,155.243..7,755.488 rows=1,452,661 loops=1)

  • Sort Key: sipd.patient_indent_no, sipd.status, sipd.indent_item_id, ip.priority, ip.verbal_order
  • Sort Method: external merge Disk: 43960kB
7. 1,252.772 3,380.746 ↑ 1.0 1,452,661 1

Hash Join (cost=197,350.63..260,209.15 rows=1,467,843 width=17) (actual time=1,817.202..3,380.746 rows=1,452,661 loops=1)

  • Hash Cond: (sipd.patient_presc_id = ip.prescription_id)
8. 313.782 313.782 ↓ 1.0 1,467,873 1

Seq Scan on store_indent_prescription_details sipd (cost=0.00..26,902.43 rows=1,467,843 width=19) (actual time=0.014..313.782 rows=1,467,873 loops=1)

9. 647.335 1,814.192 ↑ 1.0 3,814,424 1

Hash (cost=134,768.17..134,768.17 rows=3,814,517 width=6) (actual time=1,814.192..1,814.192 rows=3,814,424 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 53596kB
10. 1,166.857 1,166.857 ↑ 1.0 3,814,424 1

Seq Scan on ip_prescription ip (cost=0.00..134,768.17 rows=3,814,517 width=6) (actual time=0.014..1,166.857 rows=3,814,424 loops=1)

11. 10,052.682 10,052.682 ↓ 9.0 564,911 1

Materialize (cost=518,964.60..574,863.48 rows=62,962 width=239) (actual time=6,677.315..10,052.682 rows=564,911 loops=1)

12. 2,411.129 9,896.305 ↓ 4.1 255,520 1

Nested Loop (cost=518,964.60..574,706.07 rows=62,962 width=239) (actual time=6,677.310..9,896.305 rows=255,520 loops=1)

13. 4,039.347 7,428.486 ↓ 4.1 255,520 1

Merge Left Join (cost=518,964.17..532,555.20 rows=62,962 width=206) (actual time=6,676.952..7,428.486 rows=255,520 loops=1)

  • Merge Cond: ((spim.patient_indent_no)::text = (foo.patient_indent_no)::text)
14. 900.403 3,389.139 ↓ 4.1 255,520 1

Sort (cost=408,668.05..408,825.46 rows=62,962 width=174) (actual time=3,270.572..3,389.139 rows=255,520 loops=1)

  • Sort Key: spim.patient_indent_no
  • Sort Method: external merge Disk: 39552kB
15. 60.440 2,488.736 ↓ 4.1 255,520 1

Hash Left Join (cost=358,796.44..403,649.29 rows=62,962 width=174) (actual time=1,664.913..2,488.736 rows=255,520 loops=1)

  • Hash Cond: ((bn.ward_no)::text = (wn.ward_no)::text)
16. 65.712 2,428.296 ↓ 4.1 255,520 1

Hash Left Join (cost=358,789.96..403,474.02 rows=62,962 width=158) (actual time=1,664.844..2,428.296 rows=255,520 loops=1)

  • Hash Cond: (adm.bed_id = bn.bed_id)
17. 135.266 2,362.584 ↓ 4.1 255,520 1

Hash Left Join (cost=358,559.90..403,078.57 rows=62,962 width=141) (actual time=1,662.964..2,362.584 rows=255,520 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (adm.patient_id)::text)
18. 334.093 2,227.318 ↓ 4.1 255,520 1

Hash Join (cost=352,176.76..396,530.16 rows=62,962 width=137) (actual time=1,606.055..2,227.318 rows=255,520 loops=1)

  • Hash Cond: ((spim.visit_id)::text = (pr.patient_id)::text)
19. 299.535 299.535 ↑ 1.0 255,524 1

Seq Scan on store_patient_indent_main spim (cost=0.00..22,856.53 rows=257,477 width=118) (actual time=5.839..299.535 rows=255,524 loops=1)

  • Filter: (indent_store = 210)
  • Rows Removed by Filter: 539174
20. 337.299 1,593.690 ↓ 1.0 1,522,078 1

Hash (cost=321,578.24..321,578.24 rows=1,506,361 width=35) (actual time=1,593.690..1,593.690 rows=1,522,078 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 33462kB
21. 1,157.746 1,256.391 ↓ 1.0 1,522,078 1

Bitmap Heap Scan on patient_registration pr (cost=28,202.73..321,578.24 rows=1,506,361 width=35) (actual time=157.720..1,256.391 rows=1,522,078 loops=1)

  • Recheck Cond: (center_id = 9)
  • Heap Blocks: exact=266376
22. 98.645 98.645 ↓ 1.0 1,522,655 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..27,826.14 rows=1,506,361 width=0) (actual time=98.645..98.645 rows=1,522,655 loops=1)

  • Index Cond: (center_id = 9)
23. 0.000 56.690 ↑ 1.0 174,863 1

Hash (cost=4,195.84..4,195.84 rows=174,984 width=19) (actual time=56.690..56.690 rows=174,863 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10928kB
24. 29.016 29.016 ↑ 1.0 174,863 1

Seq Scan on admission adm (cost=0.00..4,195.84 rows=174,984 width=19) (actual time=0.009..29.016 rows=174,863 loops=1)

25. 0.964 1.861 ↑ 1.0 6,625 1

Hash (cost=147.25..147.25 rows=6,625 width=25) (actual time=1.861..1.861 rows=6,625 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 446kB
26. 0.897 0.897 ↑ 1.0 6,625 1

Seq Scan on bed_names bn (cost=0.00..147.25 rows=6,625 width=25) (actual time=0.008..0.897 rows=6,625 loops=1)

27. 0.030 0.060 ↓ 1.0 200 1

Hash (cost=3.99..3.99 rows=199 width=25) (actual time=0.059..0.060 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
28. 0.030 0.030 ↓ 1.0 200 1

Seq Scan on ward_names wn (cost=0.00..3.99 rows=199 width=25) (actual time=0.008..0.030 rows=200 loops=1)

29. 115.776 3,731.732 ↓ 1.7 724,953 1

Subquery Scan on foo (cost=110,296.11..122,152.85 rows=431,154 width=41) (actual time=3,398.813..3,731.732 rows=724,953 loops=1)

30. 2,599.055 3,615.956 ↓ 1.7 724,953 1

Sort (cost=110,296.11..111,374.00 rows=431,154 width=25) (actual time=3,398.808..3,615.956 rows=724,953 loops=1)

  • Sort Key: store_patient_indent_details.patient_indent_no
  • Sort Method: external merge Disk: 29800kB
31. 667.462 1,016.901 ↓ 1.7 724,953 1

HashAggregate (cost=65,633.21..69,944.75 rows=431,154 width=25) (actual time=819.538..1,016.901 rows=724,953 loops=1)

  • Group Key: store_patient_indent_details.patient_indent_no
32. 349.439 349.439 ↓ 1.0 1,640,140 1

Seq Scan on store_patient_indent_details (cost=0.00..53,358.46 rows=1,636,633 width=17) (actual time=0.014..349.439 rows=1,640,140 loops=1)

  • Filter: ((sale_item_id IS NOT NULL) OR (item_issue_no IS NOT NULL))
  • Rows Removed by Filter: 922647
33. 2,299.680 2,299.680 ↑ 1.0 1 255,520

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.67 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=255,520)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
Planning time : 8.472 ms
Execution time : 26,670.071 ms