explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SU1C

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 39,920.176 ↑ 98.0 1 1

Append (cost=3,964,835.49..6,669,023.96 rows=98 width=637) (actual time=39,920.174..39,920.176 rows=1 loops=1)

2. 0.001 202.071 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=3,964,835.49..3,964,842.87 rows=59 width=622) (actual time=202.071..202.071 rows=0 loops=1)

3. 0.003 202.070 ↓ 0.0 0 1

HashAggregate (cost=3,964,835.49..3,964,842.28 rows=59 width=622) (actual time=202.070..202.070 rows=0 loops=1)

4. 0.000 202.067 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,406.26..3,964,828.71 rows=59 width=622) (actual time=202.067..202.067 rows=0 loops=1)

5. 0.002 202.067 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,405.98..3,964,741.56 rows=59 width=614) (actual time=202.067..202.067 rows=0 loops=1)

6. 0.000 202.065 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,405.56..3,964,739.29 rows=3 width=551) (actual time=202.065..202.065 rows=0 loops=1)

7. 0.001 202.065 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,405.28..3,964,729.86 rows=3 width=549) (actual time=202.065..202.065 rows=0 loops=1)

8. 0.000 202.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,405.00..3,964,720.43 rows=3 width=544) (actual time=202.064..202.064 rows=0 loops=1)

9. 0.001 202.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,694,404.87..3,964,719.94 rows=3 width=539) (actual time=202.064..202.064 rows=0 loops=1)

10. 0.001 202.063 ↓ 0.0 0 1

Nested Loop (cost=3,694,404.59..3,964,719.02 rows=3 width=507) (actual time=202.063..202.063 rows=0 loops=1)

11. 0.002 202.062 ↓ 0.0 0 1

Nested Loop (cost=3,694,404.16..3,964,693.64 rows=3 width=504) (actual time=202.062..202.062 rows=0 loops=1)

12. 0.038 0.038 ↑ 1.0 1 1

Index Scan using idx_bill_cash_bill_no on bill_cash bcash (cost=0.42..8.44 rows=1 width=45) (actual time=0.037..0.038 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
13. 0.005 202.022 ↓ 0.0 0 1

Nested Loop (cost=3,694,403.73..3,964,685.17 rows=3 width=487) (actual time=202.022..202.022 rows=0 loops=1)

14. 0.043 0.043 ↑ 1.0 1 1

Index Scan using bill_pkey on bill b (cost=0.56..8.59 rows=1 width=31) (actual time=0.041..0.043 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
  • Filter: ((status <> 'X'::bpchar) AND ((status = 'F'::bpchar) OR (status = 'C'::bpchar)) AND ((sponsor_writeoff <> 'A'::bpchar) OR (status <> 'C'::bpchar)))
15. 0.000 201.974 ↓ 0.0 0 1

Nested Loop (cost=3,694,403.18..3,964,676.55 rows=3 width=470) (actual time=201.974..201.974 rows=0 loops=1)

16. 0.001 201.974 ↓ 0.0 0 1

Nested Loop (cost=3,694,403.04..3,964,675.88 rows=3 width=470) (actual time=201.974..201.974 rows=0 loops=1)

17. 0.000 201.973 ↓ 0.0 0 1

Nested Loop (cost=3,694,402.90..3,964,675.13 rows=3 width=468) (actual time=201.973..201.973 rows=0 loops=1)

18. 0.020 201.973 ↓ 0.0 0 1

Hash Join (cost=3,694,402.61..3,964,674.09 rows=3 width=467) (actual time=201.973..201.973 rows=0 loops=1)

  • Hash Cond: ((min(sd.sale_item_id)) = s.sale_item_id)
19. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=3,666,586.53..3,913,811.58 rows=1,676,102 width=270) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,666,586.53..3,670,776.79 rows=1,676,102 width=270) (never executed)

  • Sort Key: b_1.bill_no, bcash_1.cash_id, sd.item_code, ((sm_1.sale_date)::date), bcash_1.submission_batch_id, sd.code_type, (CASE WHEN ((sd.quantity + sd.return_qty) > 0::numeric) THEN trunc(((sd.amount + sd.return_amt) / (sd.quantity + sd.return_qty)), 2) ELSE (sd.amount + sd.return_amt) END), m_1.issue_base_unit, sd.tax_rate
21. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,001,625.78..3,281,334.92 rows=1,676,102 width=270) (never executed)

  • Hash Cond: (sd.medicine_id = m_1.medicine_id)
22. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,997,117.58..3,209,782.64 rows=1,676,102 width=211) (never executed)

  • Hash Cond: ((sd.sale_id)::text = (sm_1.sale_id)::text)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on store_sales_details sd (cost=0.00..112,011.02 rows=1,676,102 width=98) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=2,972,924.60..2,972,924.60 rows=755,278 width=131) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=438,069.45..2,972,924.60 rows=755,278 width=131) (never executed)

  • Hash Cond: ((sm_1.bill_no)::text = (bcash_1.bill_no)::text)
26. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=40,426.75..2,557,344.05 rows=755,278 width=107) (never executed)

  • Hash Cond: ((bc_1.charge_id)::text = (sm_1.charge_id)::text)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on bill_charge bc_1 (cost=0.00..1,616,286.76 rows=26,510,276 width=65) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash (cost=24,346.78..24,346.78 rows=755,278 width=42) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on store_sales_main sm_1 (cost=0.00..24,346.78 rows=755,278 width=42) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=393,984.12..393,984.12 rows=292,686 width=53) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=21.56..393,984.12 rows=292,686 width=53) (never executed)

  • Merge Cond: ((bcash_1.bill_no)::text = (b_1.bill_no)::text)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_bill_cash_bill_no on bill_cash bcash_1 (cost=0.42..34,169.24 rows=292,686 width=39) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using bill_pkey on bill b_1 (cost=0.56..348,804.36 rows=5,515,987 width=14) (never executed)

  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,886.98..3,886.98 rows=49,698 width=67) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on store_item_details m_1 (cost=0.00..3,886.98 rows=49,698 width=67) (never executed)

36. 0.001 201.953 ↓ 0.0 0 1

Hash (cost=27,816.04..27,816.04 rows=3 width=107) (actual time=201.953..201.953 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
37. 0.001 201.952 ↓ 0.0 0 1

Nested Loop (cost=632.80..27,816.04 rows=3 width=107) (actual time=201.952..201.952 rows=0 loops=1)

38. 150.319 201.951 ↓ 0.0 0 1

Hash Join (cost=632.38..27,811.49 rows=4 width=58) (actual time=201.951..201.951 rows=0 loops=1)

  • Hash Cond: ((sm.charge_id)::text = (bc.charge_id)::text)
39. 51.571 51.571 ↑ 1.0 755,278 1

Seq Scan on store_sales_main sm (cost=0.00..24,346.78 rows=755,278 width=27) (actual time=0.004..51.571 rows=755,278 loops=1)

40. 0.005 0.061 ↑ 148.0 1 1

Hash (cost=630.53..630.53 rows=148 width=51) (actual time=0.061..0.061 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
41. 0.056 0.056 ↑ 148.0 1 1

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..630.53 rows=148 width=51) (actual time=0.044..0.056 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
  • Filter: (status <> 'X'::bpchar)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using pharmacy_medicine_sales_sale_id_idx on store_sales_details s (cost=0.43..1.12 rows=2 width=67) (never executed)

  • Index Cond: ((sale_id)::text = (sm.sale_id)::text)
  • Filter: ((quantity + return_qty) > 0::numeric)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using pharmacy_medicine_details_pkey on store_item_details m (cost=0.29..0.34 rows=1 width=9) (never executed)

  • Index Cond: (medicine_id = s.medicine_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using chargehead_constants_pkey on chargehead_constants chc (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: ((chargehead_id)::text = (bc.charge_head)::text)
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using chargegroup_constants_pkey on chargegroup_constants cgc (cost=0.14..0.21 rows=1 width=4) (never executed)

  • Index Cond: (chargegroup_id = (bc.charge_group)::text)
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..8.45 rows=1 width=34) (never executed)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors doc (cost=0.28..0.30 rows=1 width=39) (never executed)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using mrd_supported_code_types_pkey on mrd_supported_code_types msct (cost=0.14..0.16 rows=1 width=14) (never executed)

  • Index Cond: ((code_type)::text = (s.code_type)::text)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors drs (cost=0.28..3.13 rows=1 width=17) (never executed)

  • Index Cond: ((doctor_id)::text = (bc.payee_doctor_id)::text)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors pdoc (cost=0.28..3.13 rows=1 width=17) (never executed)

  • Index Cond: ((doctor_id)::text = (bc.prescribing_dr_id)::text)
51. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..0.74 rows=2 width=70) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Index Scan using referral_pkey on referral (cost=0.42..0.44 rows=1 width=70) (never executed)

  • Index Cond: ((referal_no)::text = (pr.reference_docto_id)::text)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors (cost=0.28..0.30 rows=1 width=71) (never executed)

  • Index Cond: ((doctor_id)::text = (pr.reference_docto_id)::text)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using physiotherapy_user_pkey on physiotherapy_user pu (cost=0.28..1.46 rows=1 width=9) (never executed)

  • Index Cond: ((user_name)::text = (bc.physiotherapist_user)::text)
55. 0.005 39,718.102 ↑ 39.0 1 1

Subquery Scan on *SELECT* 2 (cost=2,704,177.58..2,704,181.09 rows=39 width=660) (actual time=39,718.101..39,718.102 rows=1 loops=1)

56. 0.063 39,718.097 ↑ 39.0 1 1

HashAggregate (cost=2,704,177.58..2,704,180.70 rows=39 width=660) (actual time=39,718.097..39,718.097 rows=1 loops=1)

57. 0.012 39,718.034 ↑ 39.0 1 1

Nested Loop Left Join (cost=2,472,485.15..2,704,173.09 rows=39 width=660) (actual time=38,326.881..39,718.034 rows=1 loops=1)

58. 0.017 39,718.018 ↑ 2.0 1 1

Nested Loop Left Join (cost=2,472,484.73..2,704,171.29 rows=2 width=597) (actual time=38,326.866..39,718.018 rows=1 loops=1)

59. 0.004 39,718.001 ↑ 2.0 1 1

Nested Loop Left Join (cost=2,472,484.45..2,704,163.67 rows=2 width=589) (actual time=38,326.850..39,718.001 rows=1 loops=1)

60. 0.004 39,717.988 ↑ 2.0 1 1

Nested Loop Left Join (cost=2,472,484.17..2,704,154.23 rows=2 width=587) (actual time=38,326.838..39,717.988 rows=1 loops=1)

61. 0.004 39,717.978 ↑ 2.0 1 1

Nested Loop Left Join (cost=2,472,483.90..2,704,144.79 rows=2 width=582) (actual time=38,326.828..39,717.978 rows=1 loops=1)

62. 0.003 39,717.966 ↑ 2.0 1 1

Nested Loop (cost=2,472,483.76..2,704,143.96 rows=2 width=574) (actual time=38,326.818..39,717.966 rows=1 loops=1)

63. 0.011 39,717.939 ↑ 2.0 1 1

Nested Loop (cost=2,472,483.62..2,704,143.29 rows=2 width=574) (actual time=38,326.791..39,717.939 rows=1 loops=1)

64. 0.006 39,717.909 ↑ 2.0 1 1

Nested Loop (cost=2,472,483.48..2,704,142.45 rows=2 width=572) (actual time=38,326.762..39,717.909 rows=1 loops=1)

65. 0.006 0.123 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..25.81 rows=1 width=97) (actual time=0.109..0.123 rows=1 loops=1)

66. 0.009 0.084 ↑ 1.0 1 1

Nested Loop (cost=1.41..25.50 rows=1 width=65) (actual time=0.077..0.084 rows=1 loops=1)

67. 0.007 0.047 ↑ 1.0 1 1

Nested Loop (cost=0.98..17.04 rows=1 width=62) (actual time=0.043..0.047 rows=1 loops=1)

68. 0.030 0.030 ↑ 1.0 1 1

Index Scan using bill_pkey on bill b_2 (cost=0.56..8.59 rows=1 width=31) (actual time=0.029..0.030 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
  • Filter: ((status <> 'X'::bpchar) AND ((status = 'F'::bpchar) OR (status = 'C'::bpchar)) AND ((sponsor_writeoff <> 'A'::bpchar) OR (status <> 'C'::bpchar)))
69. 0.010 0.010 ↑ 1.0 1 1

Index Scan using idx_bill_cash_bill_no on bill_cash bcash_2 (cost=0.42..8.44 rows=1 width=45) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
70. 0.028 0.028 ↑ 1.0 1 1

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..8.45 rows=1 width=34) (actual time=0.026..0.028 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (b_2.visit_id)::text)
71. 0.033 0.033 ↑ 1.0 1 1

Index Scan using doctors_pkey on doctors doc_1 (cost=0.28..0.30 rows=1 width=39) (actual time=0.027..0.033 rows=1 loops=1)

  • Index Cond: ((doctor_id)::text = (pr_1.doctor)::text)
72. 56.957 39,717.780 ↑ 2.0 1 1

Hash Join (cost=2,472,481.79..2,704,116.62 rows=2 width=503) (actual time=38,326.649..39,717.780 rows=1 loops=1)

  • Hash Cond: ((min((bc_2.charge_id)::text)) = (bcc.charge_id)::text)
73. 3,455.953 39,660.795 ↑ 1.9 687,567 1

GroupAggregate (cost=2,471,849.11..2,685,153.11 rows=1,333,150 width=179) (actual time=36,092.010..39,660.795 rows=687,567 loops=1)

74. 6,909.226 36,204.842 ↑ 1.8 728,163 1

Sort (cost=2,471,849.11..2,475,181.98 rows=1,333,150 width=179) (actual time=36,091.921..36,204.842 rows=728,163 loops=1)

  • Sort Key: b_3.bill_no, bcash_3.cash_id, ((bc_2.posted_date)::date), bcash_3.submission_batch_id, bc_2.code_type, (CASE WHEN ((bc_2.act_quantity + bc_2.return_qty) > 1::numeric) THEN trunc(((bc_2.amount + bc_2.return_amt) / (bc_2.act_quantity + bc_2.return_qty)), 2) ELSE (bc_2.amount + bc_2.return_amt) END), (CASE WHEN ((bc_2.charge_group)::text = 'DOC'::text) THEN bc_2.act_description_id ELSE bc_2.act_rate_plan_item_code END), bc_2.vat_rate
  • Sort Method: external sort Disk: 122208kB
75. 14,017.336 29,295.616 ↑ 1.8 728,163 1

Hash Join (cost=397,642.69..2,217,750.04 rows=1,333,150 width=179) (actual time=2,550.019..29,295.616 rows=728,163 loops=1)

  • Hash Cond: ((bc_2.bill_no)::text = (b_3.bill_no)::text)
76. 12,739.403 12,739.403 ↑ 1.0 25,107,518 1

Seq Scan on bill_charge bc_2 (cost=0.00..1,682,562.45 rows=25,124,672 width=154) (actual time=0.051..12,739.403 rows=25,107,518 loops=1)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 1400350
77. 93.896 2,538.877 ↑ 1.0 292,686 1

Hash (cost=393,984.12..393,984.12 rows=292,686 width=53) (actual time=2,538.877..2,538.877 rows=292,686 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 23399kB
78. 1,413.372 2,444.981 ↑ 1.0 292,686 1

Merge Join (cost=21.56..393,984.12 rows=292,686 width=53) (actual time=0.073..2,444.981 rows=292,686 loops=1)

  • Merge Cond: ((bcash_3.bill_no)::text = (b_3.bill_no)::text)
79. 166.738 166.738 ↑ 1.0 292,686 1

Index Scan using idx_bill_cash_bill_no on bill_cash bcash_3 (cost=0.42..34,169.24 rows=292,686 width=39) (actual time=0.006..166.738 rows=292,686 loops=1)

80. 864.871 864.871 ↑ 1.0 5,453,945 1

Index Only Scan using bill_pkey on bill b_3 (cost=0.56..348,804.36 rows=5,515,987 width=14) (actual time=0.026..864.871 rows=5,453,945 loops=1)

  • Heap Fetches: 15478
81. 0.003 0.028 ↑ 48.0 1 1

Hash (cost=632.09..632.09 rows=48 width=89) (actual time=0.028..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
82. 0.025 0.025 ↑ 48.0 1 1

Index Scan using bill_charge_bill_no_index on bill_charge bcc (cost=0.56..632.09 rows=48 width=89) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = 'BL091812005983'::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHMED,PHCMED,PHRET,PHCRET}'::text[])) AND ((act_quantity + return_qty) > 0::numeric))
83. 0.019 0.019 ↑ 1.0 1 1

Index Scan using chargehead_constants_pkey on chargehead_constants chc_1 (cost=0.14..0.41 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: ((chargehead_id)::text = (bcc.charge_head)::text)
84. 0.024 0.024 ↑ 1.0 1 1

Index Only Scan using chargegroup_constants_pkey on chargegroup_constants cgc_1 (cost=0.14..0.32 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)

  • Index Cond: (chargegroup_id = (bcc.charge_group)::text)
  • Heap Fetches: 0
85. 0.008 0.008 ↑ 1.0 1 1

Index Scan using mrd_supported_code_types_pkey on mrd_supported_code_types msct_1 (cost=0.14..0.40 rows=1 width=14) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((code_type)::text = (bcc.code_type)::text)
86. 0.006 0.006 ↓ 0.0 0 1

Index Scan using doctors_pkey on doctors drs_1 (cost=0.28..4.71 rows=1 width=17) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((doctor_id)::text = (bcc.payee_doctor_id)::text)
87. 0.009 0.009 ↑ 1.0 1 1

Index Scan using doctors_pkey on doctors pdoc_1 (cost=0.28..4.71 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((doctor_id)::text = (bcc.prescribing_dr_id)::text)
88. 0.000 0.000 ↓ 0.0 0 1

Index Scan using physiotherapy_user_pkey on physiotherapy_user pu_1 (cost=0.28..3.80 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: ((user_name)::text = (bcc.physiotherapist_user)::text)
89. 0.003 0.004 ↓ 0.0 0 1

Append (cost=0.42..0.74 rows=2 width=70) (actual time=0.004..0.004 rows=0 loops=1)

90. 0.001 0.001 ↓ 0.0 0 1

Index Scan using referral_pkey on referral referral_1 (cost=0.42..0.44 rows=1 width=70) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((referal_no)::text = (pr_1.reference_docto_id)::text)
91. 0.000 0.000 ↓ 0.0 0 1

Index Scan using doctors_pkey on doctors doctors_1 (cost=0.28..0.30 rows=1 width=71) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: ((doctor_id)::text = (pr_1.reference_docto_id)::text)