explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sutx

Settings
# exclusive inclusive rows x rows loops node
1. 0.130 64,119.366 ↓ 5.5 172 1

Sort (cost=7,896,533.90..7,896,533.98 rows=31 width=668) (actual time=64,119.335..64,119.366 rows=172 loops=1)

  • Sort Key: q0.invoice_date
  • Sort Method: quicksort Memory: 68kB
2. 31.004 64,119.236 ↓ 5.5 172 1

Subquery Scan on q0 (cost=7,806,596.88..7,896,533.13 rows=31 width=668) (actual time=63,911.008..64,119.236 rows=172 loops=1)

  • Filter: ((q0.grn_date >= '2019-12-01'::date) AND (q0.grn_date <= '2019-12-31'::date) AND ((q0.center_name)::text = 'Spectra Koramangala'::text))
  • Rows Removed by Filter: 266297
3. 613.517 64,088.232 ↑ 4.7 266,469 1

WindowAgg (cost=7,806,596.88..7,874,824.38 rows=1,240,500 width=928) (actual time=63,071.326..64,088.232 rows=266,469 loops=1)

4. 2,312.539 63,474.715 ↑ 4.7 266,469 1

Sort (cost=7,806,596.88..7,809,698.13 rows=1,240,500 width=928) (actual time=63,071.291..63,474.715 rows=266,469 loops=1)

  • Sort Key: store_purchase_invoice_report_view.grn_nos
  • Sort Method: external merge Disk: 47008kB
5. 58.117 61,162.176 ↑ 4.7 266,469 1

Subquery Scan on store_purchase_invoice_report_view (cost=807,509.41..6,167,362.84 rows=1,240,500 width=928) (actual time=42,424.880..61,162.176 rows=266,469 loops=1)

6. 43.575 61,104.059 ↑ 4.7 266,469 1

Append (cost=807,509.41..6,154,957.84 rows=1,240,500 width=267) (actual time=42,424.879..61,104.059 rows=266,469 loops=1)

7. 109.349 58,470.333 ↑ 4.7 258,894 1

Subquery Scan on *SELECT* 1 (cost=807,509.41..1,338,537.49 rows=1,206,882 width=268) (actual time=42,424.878..58,470.333 rows=258,894 loops=1)

8. 7,797.476 58,360.984 ↑ 4.7 258,894 1

GroupAggregate (cost=807,509.41..1,326,468.67 rows=1,206,882 width=268) (actual time=42,424.876..58,360.984 rows=258,894 loops=1)

9. 46,222.570 50,563.508 ↓ 1.0 1,213,778 1

Sort (cost=807,509.41..810,526.62 rows=1,206,882 width=268) (actual time=42,424.628..50,563.508 rows=1,213,778 loops=1)

  • Sort Key: i.supplier_invoice_id, sm.supplier_name, sm.cust_supplier_code, i.invoice_no, i.invoice_date, i.po_no, i.status, i.due_date, i.paid_date, i.tax_name, i.discount_type, i.discount_per, i.discount, i.round_off, i.other_charges, i.cess_tax_amt, stm.store_type_name, i.cess_tax_rate, i.cash_purchase, spm.po_date, gm.consignment_stock, gm.user_name, i.date_time, s.center_id, hcm.center_name, gm.form_8h, sm.supplier_state, sm.supplier_tin_no, s.pharmacy_tin_no, sm.drug_license_no, sm.pan_no, sm.cin_no
  • Sort Method: external merge Disk: 368792kB
10. 408.933 4,340.938 ↓ 1.0 1,213,778 1

Hash Left Join (cost=79,426.14..227,702.61 rows=1,206,882 width=268) (actual time=1,305.346..4,340.938 rows=1,213,778 loops=1)

  • Hash Cond: (s.store_type_id = stm.store_type_id)
11. 1,455.756 3,931.999 ↓ 1.0 1,213,778 1

Hash Join (cost=79,425.03..211,106.87 rows=1,206,882 width=266) (actual time=1,305.325..3,931.999 rows=1,213,778 loops=1)

  • Hash Cond: ((g.grn_no)::text = (gm.grn_no)::text)
12. 693.912 1,227.818 ↓ 1.0 1,244,315 1

Hash Join (cost=5,078.21..85,461.23 rows=1,242,134 width=47) (actual time=55.365..1,227.818 rows=1,244,315 loops=1)

  • Hash Cond: (g.medicine_id = pmd.medicine_id)
13. 478.651 478.651 ↓ 1.0 1,244,315 1

Seq Scan on store_grn_details g (cost=0.00..37,905.34 rows=1,242,134 width=46) (actual time=0.010..478.651 rows=1,244,315 loops=1)

14. 19.118 55.255 ↓ 1.0 93,493 1

Hash (cost=3,452.76..3,452.76 rows=93,476 width=9) (actual time=55.255..55.255 rows=93,493 loops=1)

  • Buckets: 8192 Batches: 4 Memory Usage: 1014kB
15. 36.137 36.137 ↓ 1.0 93,493 1

Seq Scan on store_item_details pmd (cost=0.00..3,452.76 rows=93,476 width=9) (actual time=0.012..36.137 rows=93,493 loops=1)

16. 208.081 1,248.425 ↓ 1.0 258,894 1

Hash (cost=63,037.84..63,037.84 rows=258,478 width=231) (actual time=1,248.425..1,248.425 rows=258,894 loops=1)

  • Buckets: 1024 Batches: 64 Memory Usage: 1175kB
17. 134.896 1,040.344 ↓ 1.0 258,894 1

Hash Join (cost=14,151.58..63,037.84 rows=258,478 width=231) (actual time=124.652..1,040.344 rows=258,894 loops=1)

  • Hash Cond: ((i.supplier_id)::text = (sm.supplier_code)::text)
18. 99.975 901.071 ↓ 1.0 258,894 1

Hash Join (cost=13,846.69..57,886.49 rows=258,478 width=176) (actual time=120.259..901.071 rows=258,894 loops=1)

  • Hash Cond: (gm.store_id = s.dept_id)
19. 288.133 798.113 ↓ 1.0 258,894 1

Hash Left Join (cost=13,621.44..53,460.97 rows=258,478 width=121) (actual time=117.260..798.113 rows=258,894 loops=1)

  • Hash Cond: ((i.po_no)::text = (spm.po_no)::text)
20. 183.181 392.856 ↓ 1.0 258,894 1

Merge Join (cost=0.89..25,840.58 rows=258,478 width=117) (actual time=0.030..392.856 rows=258,894 loops=1)

  • Merge Cond: (i.supplier_invoice_id = gm.supplier_invoice_id)
21. 104.419 104.419 ↓ 1.0 258,894 1

Index Scan using supp_inv_id_indx on store_invoice i (cost=0.42..11,384.98 rows=258,478 width=83) (actual time=0.014..104.419 rows=258,894 loops=1)

22. 105.256 105.256 ↑ 1.0 258,895 1

Index Scan using supp_inv_id on store_grn_main gm (cost=0.42..10,888.84 rows=266,028 width=38) (actual time=0.009..105.256 rows=258,895 loops=1)

23. 38.877 117.124 ↓ 1.0 171,961 1

Hash (cost=10,634.58..10,634.58 rows=171,758 width=15) (actual time=117.124..117.124 rows=171,961 loops=1)

  • Buckets: 8192 Batches: 8 Memory Usage: 1010kB
24. 78.247 78.247 ↓ 1.0 171,961 1

Seq Scan on store_po_main spm (cost=0.00..10,634.58 rows=171,758 width=15) (actual time=0.011..78.247 rows=171,961 loops=1)

25. 0.705 2.983 ↑ 1.0 2,613 1

Hash (cost=192.59..192.59 rows=2,613 width=63) (actual time=2.983..2.983 rows=2,613 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 237kB
26. 1.183 2.278 ↑ 1.0 2,613 1

Hash Left Join (cost=29.53..192.59 rows=2,613 width=63) (actual time=0.322..2.278 rows=2,613 loops=1)

  • Hash Cond: (s.center_id = hcm.center_id)
27. 0.790 0.790 ↑ 1.0 2,613 1

Seq Scan on stores s (cost=0.00..127.13 rows=2,613 width=45) (actual time=0.003..0.790 rows=2,613 loops=1)

28. 0.096 0.305 ↑ 1.0 468 1

Hash (cost=23.68..23.68 rows=468 width=22) (actual time=0.305..0.305 rows=468 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
29. 0.209 0.209 ↑ 1.0 468 1

Seq Scan on hospital_center_master hcm (cost=0.00..23.68 rows=468 width=22) (actual time=0.004..0.209 rows=468 loops=1)

30. 1.589 4.377 ↓ 1.0 5,285 1

Hash (cost=238.84..238.84 rows=5,284 width=69) (actual time=4.377..4.377 rows=5,285 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 476kB
31. 2.788 2.788 ↓ 1.0 5,285 1

Seq Scan on supplier_master sm (cost=0.00..238.84 rows=5,284 width=69) (actual time=0.005..2.788 rows=5,285 loops=1)

32. 0.002 0.006 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=10) (actual time=0.006..0.006 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on store_type_master stm (cost=0.00..1.05 rows=5 width=10) (actual time=0.003..0.004 rows=5 loops=1)

34. 3.551 2,590.151 ↑ 4.4 7,575 1

Subquery Scan on *SELECT* 2 (cost=24,311.74..4,816,420.34 rows=33,618 width=240) (actual time=1,674.671..2,590.151 rows=7,575 loops=1)

35. 594.070 2,586.600 ↑ 4.4 7,575 1

GroupAggregate (cost=24,311.74..4,816,084.16 rows=33,618 width=240) (actual time=1,674.665..2,586.600 rows=7,575 loops=1)

36. 1,413.855 1,992.530 ↑ 1.1 30,537 1

Sort (cost=24,311.74..24,395.78 rows=33,618 width=240) (actual time=1,674.474..1,992.530 rows=30,537 loops=1)

  • Sort Key: sm_1.supplier_name, sm_1.cust_supplier_code, d.debit_note_no, d.debit_note_date, d.status, d.discount, d.round_off, d.other_charges, d.tax_name, stm_1.store_type_name, d.received_debit_amt, d.discount_type, d.discount_per, gm_1.consignment_stock, gm_1.user_name, d.date_time, s_1.center_id, hcm_1.center_name, gm_1.form_8h, sm_1.supplier_state, sm_1.supplier_tin_no, s_1.pharmacy_tin_no, sm_1.drug_license_no, sm_1.pan_no, sm_1.cin_no
  • Sort Method: external merge Disk: 8224kB
37. 15.494 200.025 ↑ 1.1 30,537 1

Hash Join (cost=4,537.90..17,990.18 rows=33,618 width=240) (actual time=39.927..200.025 rows=30,537 loops=1)

  • Hash Cond: (gm_1.store_id = s_1.dept_id)
38. 24.306 184.531 ↑ 1.1 30,537 1

Hash Join (cost=4,275.61..17,181.59 rows=33,618 width=183) (actual time=36.762..184.531 rows=30,537 loops=1)

  • Hash Cond: (g_1.medicine_id = pmd_1.medicine_id)
39. 17.129 124.290 ↑ 1.1 30,537 1

Nested Loop (cost=306.73..10,400.35 rows=33,618 width=187) (actual time=3.925..124.290 rows=30,537 loops=1)

40. 3.813 31.411 ↓ 1.1 7,575 1

Hash Join (cost=306.30..1,191.44 rows=7,200 width=141) (actual time=3.527..31.411 rows=7,575 loops=1)

  • Hash Cond: ((d.supplier_id)::text = (sm_1.supplier_code)::text)
41. 13.246 24.109 ↓ 1.1 7,575 1

Merge Join (cost=1.41..751.55 rows=7,200 width=86) (actual time=0.026..24.109 rows=7,575 loops=1)

  • Merge Cond: ((d.debit_note_no)::text = (gm_1.debit_note_no)::text)
42. 3.635 3.635 ↑ 1.0 7,575 1

Index Scan using dbt_pk on store_debit_note d (cost=0.28..338.02 rows=7,613 width=52) (actual time=0.010..3.635 rows=7,575 loops=1)

43. 7.228 7.228 ↑ 35.1 7,576 1

Index Scan using idx_debit_note_no on store_grn_main gm_1 (cost=0.42..11,303.46 rows=266,028 width=41) (actual time=0.009..7.228 rows=7,576 loops=1)

44. 1.602 3.489 ↓ 1.0 5,285 1

Hash (cost=238.84..238.84 rows=5,284 width=69) (actual time=3.489..3.489 rows=5,285 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 476kB
45. 1.887 1.887 ↓ 1.0 5,285 1

Seq Scan on supplier_master sm_1 (cost=0.00..238.84 rows=5,284 width=69) (actual time=0.003..1.887 rows=5,285 loops=1)

46. 75.750 75.750 ↑ 3.2 4 7,575

Index Scan using store_grn_details_grn_no_idx on store_grn_details g_1 (cost=0.43..1.15 rows=13 width=58) (actual time=0.007..0.010 rows=4 loops=7,575)

  • Index Cond: ((grn_no)::text = (gm_1.grn_no)::text)
47. 18.131 32.779 ↓ 1.0 93,493 1

Hash (cost=2,434.43..2,434.43 rows=93,476 width=4) (actual time=32.779..32.779 rows=93,493 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 1653kB
48. 14.648 14.648 ↓ 1.0 93,493 1

Index Only Scan using pharmacy_medicine_details_pkey on store_item_details pmd_1 (cost=0.29..2,434.43 rows=93,476 width=4) (actual time=0.041..14.648 rows=93,493 loops=1)

  • Heap Fetches: 2099
49. 0.774 3.156 ↑ 1.0 2,613 1

Hash (cost=229.63..229.63 rows=2,613 width=65) (actual time=3.156..3.156 rows=2,613 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 251kB
50. 0.707 2.382 ↑ 1.0 2,613 1

Hash Left Join (cost=30.64..229.63 rows=2,613 width=65) (actual time=0.219..2.382 rows=2,613 loops=1)

  • Hash Cond: (s_1.store_type_id = stm_1.store_type_id)
51. 1.031 1.670 ↑ 1.0 2,613 1

Hash Left Join (cost=29.53..192.59 rows=2,613 width=63) (actual time=0.208..1.670 rows=2,613 loops=1)

  • Hash Cond: (s_1.center_id = hcm_1.center_id)
52. 0.441 0.441 ↑ 1.0 2,613 1

Seq Scan on stores s_1 (cost=0.00..127.13 rows=2,613 width=45) (actual time=0.004..0.441 rows=2,613 loops=1)

53. 0.078 0.198 ↑ 1.0 468 1

Hash (cost=23.68..23.68 rows=468 width=22) (actual time=0.198..0.198 rows=468 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
54. 0.120 0.120 ↑ 1.0 468 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..23.68 rows=468 width=22) (actual time=0.002..0.120 rows=468 loops=1)

55. 0.002 0.005 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=10) (actual time=0.005..0.005 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
56. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on store_type_master stm_1 (cost=0.00..1.05 rows=5 width=10) (actual time=0.002..0.003 rows=5 loops=1)

57.          

SubPlan (for Sort)

58. 37.865 196.898 ↑ 1.0 1 7,573

HashAggregate (cost=71.05..71.06 rows=1 width=29) (actual time=0.026..0.026 rows=1 loops=7,573)

59. 15.146 159.033 ↑ 1.2 4 7,573

Nested Loop (cost=1.13..70.96 rows=5 width=29) (actual time=0.018..0.021 rows=4 loops=7,573)

60. 7.573 83.303 ↑ 1.0 1 7,573

Nested Loop (cost=0.70..16.75 rows=1 width=21) (actual time=0.010..0.011 rows=1 loops=7,573)

61. 37.865 37.865 ↑ 1.0 1 7,573

Index Scan using dbt_pk on store_debit_note sdn1 (cost=0.28..8.30 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=7,573)

  • Index Cond: ((debit_note_no)::text = (d.debit_note_no)::text)
62. 37.865 37.865 ↑ 1.0 1 7,573

Index Scan using idx_debit_note_no on store_grn_main gm1 (cost=0.42..8.44 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=7,573)

  • Index Cond: ((debit_note_no)::text = (d.debit_note_no)::text)
63. 60.584 60.584 ↑ 3.2 4 7,573

Index Scan using store_grn_details_grn_no_idx on store_grn_details gd1 (cost=0.43..54.08 rows=13 width=31) (actual time=0.007..0.008 rows=4 loops=7,573)

  • Index Cond: ((grn_no)::text = (gm1.grn_no)::text)
64. 37.865 181.752 ↑ 1.0 1 7,573

HashAggregate (cost=71.06..71.08 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=7,573)

65. 15.146 143.887 ↑ 1.2 4 7,573

Nested Loop (cost=1.13..70.96 rows=5 width=32) (actual time=0.016..0.019 rows=4 loops=7,573)

66. 7.573 75.730 ↑ 1.0 1 7,573

Nested Loop (cost=0.70..16.75 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=7,573)

67. 30.292 30.292 ↑ 1.0 1 7,573

Index Scan using dbt_pk on store_debit_note sdn2 (cost=0.28..8.30 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=7,573)

  • Index Cond: ((debit_note_no)::text = (d.debit_note_no)::text)
68. 37.865 37.865 ↑ 1.0 1 7,573

Index Scan using idx_debit_note_no on store_grn_main gm2 (cost=0.42..8.44 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=7,573)

  • Index Cond: ((debit_note_no)::text = (d.debit_note_no)::text)
69. 53.011 53.011 ↑ 3.2 4 7,573

Index Scan using store_grn_details_grn_no_idx on store_grn_details png1 (cost=0.43..54.08 rows=13 width=34) (actual time=0.006..0.007 rows=4 loops=7,573)

  • Index Cond: ((grn_no)::text = (gm2.grn_no)::text)