explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x9p3

Settings
# exclusive inclusive rows x rows loops node
1. 0.572 12.615 ↑ 58.0 1 1

GroupAggregate (cost=57,666.54..627,534.45 rows=58 width=794) (actual time=12.615..12.615 rows=1 loops=1)

  • Group Key: m_product.m_product_id, c_invoice.em_hip_yearinvoiced, c_invoiceline.ad_client_id, gen.m_product_id
2. 0.132 10.009 ↑ 1.9 30 1

Sort (cost=57,666.54..57,666.69 rows=58 width=190) (actual time=10.007..10.009 rows=30 loops=1)

  • Sort Key: c_invoice.em_hip_yearinvoiced, c_invoiceline.ad_client_id, gen.m_product_id
  • Sort Method: quicksort Memory: 32kB
3. 0.133 9.877 ↑ 1.9 30 1

Nested Loop (cost=6.13..57,664.84 rows=58 width=190) (actual time=1.620..9.877 rows=30 loops=1)

  • Join Filter: (COALESCE((SubPlan 12), '0'::numeric) <> '0'::numeric)
4. 0.013 0.163 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.83..16.87 rows=1 width=146) (actual time=0.161..0.163 rows=1 loops=1)

5. 0.062 0.062 ↑ 1.0 1 1

Index Scan using m_product_key on m_product (cost=0.42..8.44 rows=1 width=146) (actual time=0.060..0.062 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = '70ce2541ed0948de9780c24b75a545ff'::text)
6. 0.088 0.088 ↑ 1.0 1 1

Index Only Scan using m_product_key on m_product gen (cost=0.42..8.44 rows=1 width=33) (actual time=0.088..0.088 rows=1 loops=1)

  • Index Cond: (m_product_id = (m_product.generic_product_id)::text)
  • Heap Fetches: 1
7. 0.095 0.491 ↑ 1.9 30 1

Nested Loop (cost=5.30..722.66 rows=58 width=77) (actual time=0.129..0.491 rows=30 loops=1)

8. 0.075 0.126 ↑ 1.9 30 1

Bitmap Heap Scan on c_invoiceline (cost=4.87..232.70 rows=58 width=105) (actual time=0.071..0.126 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = '70ce2541ed0948de9780c24b75a545ff'::text)
  • Heap Blocks: exact=30
9. 0.051 0.051 ↑ 1.9 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.86 rows=58 width=0) (actual time=0.051..0.051 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = '70ce2541ed0948de9780c24b75a545ff'::text)
10. 0.270 0.270 ↑ 1.0 1 30

Index Scan using c_invoice_key on c_invoice (cost=0.42..8.45 rows=1 width=38) (actual time=0.009..0.009 rows=1 loops=30)

  • Index Cond: ((c_invoice_id)::text = (c_invoiceline.c_invoice_id)::text)
  • Filter: (((docstatus)::text <> 'DR'::text) AND (issotrx = 'Y'::bpchar))
11.          

SubPlan (forNested Loop)

12. 0.360 9.090 ↑ 1.0 1 30

Aggregate (cost=981.45..981.46 rows=1 width=32) (actual time=0.303..0.303 rows=1 loops=30)

13. 0.810 8.730 ↓ 1.5 30 30

Nested Loop (cost=5.46..981.40 rows=20 width=5) (actual time=0.023..0.291 rows=30 loops=30)

14. 1.290 1.620 ↑ 2.6 30 30

Bitmap Heap Scan on c_invoiceline invline_10 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.054 rows=30 loops=30)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=900
15. 0.330 0.330 ↑ 2.6 30 30

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.011..0.011 rows=30 loops=30)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
16. 6.300 6.300 ↑ 1.0 1 900

Index Scan using c_invoice_key on c_invoice inv_10 (cost=0.42..8.45 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=900)

  • Index Cond: ((c_invoice_id)::text = (invline_10.c_invoice_id)::text)
  • Filter: ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text)
17.          

SubPlan (forGroupAggregate)

18. 0.002 0.050 ↑ 1.0 1 1

Limit (cost=8.45..8.45 rows=1 width=41) (actual time=0.049..0.050 rows=1 loops=1)

19. 0.019 0.048 ↑ 1.0 1 1

Sort (cost=8.45..8.45 rows=1 width=41) (actual time=0.048..0.048 rows=1 loops=1)

  • Sort Key: m_product_po.created DESC
  • Sort Method: quicksort Memory: 25kB
20. 0.029 0.029 ↑ 1.0 1 1

Index Scan using m_prod_po_product_idx on m_product_po (cost=0.42..8.44 rows=1 width=41) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Filter: (iscurrentvendor = 'Y'::bpchar)
21. 0.002 0.204 ↑ 1.0 1 1

Aggregate (cost=981.94..981.95 rows=1 width=32) (actual time=0.204..0.204 rows=1 loops=1)

22. 0.011 0.202 ↓ 0.0 0 1

Nested Loop (cost=5.46..981.93 rows=1 width=5) (actual time=0.202..0.202 rows=0 loops=1)

23. 0.028 0.041 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline (cost=5.04..314.24 rows=79 width=38) (actual time=0.019..0.041 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
24. 0.013 0.013 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.013..0.013 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
25. 0.150 0.150 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv (cost=0.42..8.45 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '5D7F804DE3814CDAABAB2CB68F5C474D'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
26. 0.010 0.203 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.203..0.203 rows=1 loops=1)

27. 0.009 0.193 ↑ 1.0 1 1

Nested Loop (cost=5.46..981.60 rows=1 width=5) (actual time=0.154..0.193 rows=1 loops=1)

28. 0.024 0.034 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_1 (cost=5.04..314.24 rows=79 width=38) (actual time=0.016..0.034 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
29. 0.010 0.010 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.010..0.010 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
30. 0.150 0.150 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_1 (cost=0.42..8.45 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_1.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '5F143329E05F44659DD584AD39BB0F04'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
31. 0.008 0.192 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.192..0.192 rows=1 loops=1)

32. 0.001 0.184 ↓ 1.3 4 1

Nested Loop (cost=5.46..981.59 rows=3 width=5) (actual time=0.085..0.184 rows=4 loops=1)

33. 0.023 0.033 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_2 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.033 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
34. 0.010 0.010 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.010..0.010 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
35. 0.150 0.150 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_2 (cost=0.42..8.45 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_2.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '4B1D21E40C3A4A91A6F4D70E1528FDAF'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
36. 0.010 0.205 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.205..0.205 rows=1 loops=1)

37. 0.030 0.195 ↓ 1.7 5 1

Nested Loop (cost=5.46..981.59 rows=3 width=5) (actual time=0.031..0.195 rows=5 loops=1)

38. 0.031 0.045 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_3 (cost=5.04..314.24 rows=79 width=38) (actual time=0.018..0.045 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
39. 0.014 0.014 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.014..0.014 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
40. 0.120 0.120 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_3 (cost=0.42..8.45 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_3.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '69C70CBA08B7441C9573CE8C014BDAA3'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
41. 0.008 0.187 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.187..0.187 rows=1 loops=1)

42. 0.028 0.179 ↑ 1.0 2 1

Nested Loop (cost=5.46..981.59 rows=2 width=5) (actual time=0.079..0.179 rows=2 loops=1)

43. 0.021 0.031 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_4 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.031 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
44. 0.010 0.010 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.010..0.010 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
45. 0.120 0.120 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_4 (cost=0.42..8.45 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_4.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = 'EB49330C8CB94BCBB509CFBC59C52B6C'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
46. 0.009 0.186 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.186..0.186 rows=1 loops=1)

47. 0.024 0.177 ↓ 3.0 3 1

Nested Loop (cost=5.46..981.59 rows=1 width=5) (actual time=0.129..0.177 rows=3 loops=1)

48. 0.024 0.033 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_5 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.033 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
49. 0.009 0.009 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.009..0.009 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
50. 0.120 0.120 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_5 (cost=0.42..8.45 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_5.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '34DAD183BBDE4F319D7988210EB0D7BD'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
51. 0.008 0.191 ↑ 1.0 1 1

Aggregate (cost=981.61..981.62 rows=1 width=32) (actual time=0.191..0.191 rows=1 loops=1)

52. 0.026 0.183 ↓ 1.8 7 1

Nested Loop (cost=5.46..981.59 rows=4 width=5) (actual time=0.040..0.183 rows=7 loops=1)

53. 0.027 0.037 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_6 (cost=5.04..314.24 rows=79 width=38) (actual time=0.018..0.037 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
54. 0.010 0.010 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.009..0.010 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
55. 0.120 0.120 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_6 (cost=0.42..8.45 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_6.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = 'CAE21A8CCB274F10A471C92FC369A45F'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
56. 0.009 0.205 ↑ 1.0 1 1

Aggregate (cost=981.60..981.61 rows=1 width=32) (actual time=0.205..0.205 rows=1 loops=1)

57. 0.011 0.196 ↑ 1.0 2 1

Nested Loop (cost=5.46..981.59 rows=2 width=5) (actual time=0.031..0.196 rows=2 loops=1)

58. 0.024 0.035 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_7 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.035 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
59. 0.011 0.011 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.011..0.011 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
60. 0.150 0.150 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_7 (cost=0.42..8.45 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_7.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '01D7CCD4B85648C7968EAAFE17833306'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
61. 0.012 0.208 ↑ 1.0 1 1

Aggregate (cost=981.61..981.62 rows=1 width=32) (actual time=0.208..0.208 rows=1 loops=1)

62. 0.012 0.196 ↓ 1.2 6 1

Nested Loop (cost=5.46..981.59 rows=5 width=5) (actual time=0.051..0.196 rows=6 loops=1)

63. 0.023 0.034 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_8 (cost=5.04..314.24 rows=79 width=38) (actual time=0.016..0.034 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
64. 0.011 0.011 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.011..0.011 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
65. 0.150 0.150 ↓ 0.0 0 30

Index Scan using c_invoice_key on c_invoice inv_8 (cost=0.42..8.45 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_8.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '615CBF002A4A447D89F4359D4615B074'::text) AND ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text))
  • Rows Removed by Filter: 1
66. 0.016 0.203 ↑ 1.0 1 1

Aggregate (cost=981.45..981.46 rows=1 width=32) (actual time=0.203..0.203 rows=1 loops=1)

67. 0.033 0.187 ↓ 1.5 30 1

Nested Loop (cost=5.46..981.40 rows=20 width=5) (actual time=0.026..0.187 rows=30 loops=1)

68. 0.024 0.034 ↑ 2.6 30 1

Bitmap Heap Scan on c_invoiceline invline_9 (cost=5.04..314.24 rows=79 width=38) (actual time=0.014..0.034 rows=30 loops=1)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
69. 0.010 0.010 ↑ 2.6 30 1

Bitmap Index Scan on c_invoiceline_product (cost=0.00..5.02 rows=79 width=0) (actual time=0.010..0.010 rows=30 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
70. 0.120 0.120 ↑ 1.0 1 30

Index Scan using c_invoice_key on c_invoice inv_9 (cost=0.42..8.45 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=30)

  • Index Cond: ((c_invoice_id)::text = (invline_9.c_invoice_id)::text)
  • Filter: ((em_hip_yearinvoiced)::text = (c_invoice.em_hip_yearinvoiced)::text)