explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EsfA

Settings
# exclusive inclusive rows x rows loops node
1. 0.081 16.215 ↑ 58.0 1 1

Group (cost=57,668.97..627,506.86 rows=58 width=591) (actual time=16.198..16.215 rows=1 loops=1)

  • Group Key: m_product.m_product_id, c_invoice.em_hip_yearinvoiced, m_product.value, m_product.name, c_invoiceline.ad_client_id, m_product.m_product_category_id, gen.m_product_id, c_invoice.em_hip_yearinvoiced
2. 0.107 11.936 ↑ 1.9 30 1

Sort (cost=57,668.97..57,669.11 rows=58 width=189) (actual time=11.932..11.936 rows=30 loops=1)

  • Sort Key: m_product.m_product_id, c_invoice.em_hip_yearinvoiced, m_product.value, m_product.name, c_invoiceline.ad_client_id, m_product.m_product_category_id, gen.m_product_id, c_invoice.em_hip_yearinvoiced
  • Sort Method: quicksort Memory: 32kB
3. 0.154 11.829 ↑ 1.9 30 1

Sort (cost=57,666.54..57,666.69 rows=58 width=189) (actual time=11.824..11.829 rows=30 loops=1)

  • Sort Key: c_invoice.em_hip_yearinvoiced, c_invoiceline.ad_client_id, m_product.m_product_category_id, gen.m_product_id
  • Sort Method: quicksort Memory: 32kB
4. 0.138 11.675 ↑ 1.9 30 1

Nested Loop (cost=6.13..57,664.84 rows=58 width=189) (actual time=1.089..11.675 rows=30 loops=1)

  • Join Filter: (COALESCE((SubPlan 12), '0'::numeric) <> '0'::numeric)
5. 0.008 0.085 ↑ 1.0 1 1

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

6. 0.047 0.047 ↑ 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.045..0.047 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = '70ce2541ed0948de9780c24b75a545ff'::text)
7. 0.030 0.030 ↑ 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.030..0.030 rows=1 loops=1)

  • Index Cond: (m_product_id = (m_product.generic_product_id)::text)
  • Heap Fetches: 1
8. 0.119 0.532 ↑ 1.9 30 1

Nested Loop (cost=5.30..722.66 rows=58 width=71) (actual time=0.081..0.532 rows=30 loops=1)

9. 0.079 0.113 ↑ 1.9 30 1

Bitmap Heap Scan on c_invoiceline (cost=4.87..232.70 rows=58 width=99) (actual time=0.047..0.113 rows=30 loops=1)

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

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

  • Index Cond: ((m_product_id)::text = '70ce2541ed0948de9780c24b75a545ff'::text)
11. 0.300 0.300 ↑ 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.010..0.010 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))
12.          

SubPlan (forNested Loop)

13. 0.450 10.920 ↑ 1.0 1 30

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

14. 0.570 10.470 ↓ 1.5 30 30

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

15. 1.440 1.800 ↑ 2.6 30 30

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
17. 8.100 8.100 ↑ 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.009..0.009 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)
18.          

SubPlan (forGroup)

19. 0.005 0.153 ↑ 1.0 1 1

Limit (cost=8.45..8.45 rows=1 width=41) (actual time=0.151..0.153 rows=1 loops=1)

20. 0.056 0.148 ↑ 1.0 1 1

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

  • Sort Key: m_product_po.created DESC
  • Sort Method: quicksort Memory: 25kB
21. 0.092 0.092 ↑ 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.090..0.092 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Filter: (iscurrentvendor = 'Y'::bpchar)
22. 0.008 0.666 ↑ 1.0 1 1

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

23. 0.044 0.658 ↓ 0.0 0 1

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

24. 0.094 0.134 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
26. 0.480 0.480 ↓ 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.016..0.016 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
27. 0.020 0.634 ↑ 1.0 1 1

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

28. 0.067 0.614 ↑ 1.0 1 1

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

29. 0.097 0.127 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
31. 0.420 0.420 ↓ 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.014..0.014 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
32. 0.020 0.621 ↑ 1.0 1 1

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

33. 0.048 0.601 ↓ 1.3 4 1

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

34. 0.082 0.103 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
36. 0.450 0.450 ↓ 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.015..0.015 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
37. 0.017 0.466 ↑ 1.0 1 1

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

38. 0.056 0.449 ↓ 1.7 5 1

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

39. 0.067 0.093 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
41. 0.300 0.300 ↓ 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.010..0.010 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
42. 0.012 0.393 ↑ 1.0 1 1

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

43. 0.034 0.381 ↑ 1.0 2 1

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

44. 0.061 0.077 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
46. 0.270 0.270 ↓ 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.009..0.009 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
47. 0.007 0.306 ↑ 1.0 1 1

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

48. 0.035 0.299 ↓ 3.0 3 1

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

49. 0.038 0.054 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
51. 0.210 0.210 ↓ 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.007..0.007 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
52. 0.006 0.255 ↑ 1.0 1 1

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

53. 0.031 0.249 ↓ 1.8 7 1

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

54. 0.029 0.038 ↑ 2.6 30 1

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

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=30
55. 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)
56. 0.180 0.180 ↓ 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.006..0.006 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
57. 0.005 0.235 ↑ 1.0 1 1

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

58. 0.015 0.230 ↑ 1.0 2 1

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

59. 0.027 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.012..0.035 rows=30 loops=1)

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
61. 0.180 0.180 ↓ 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.006..0.006 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
62. 0.005 0.230 ↑ 1.0 1 1

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

63. 0.010 0.225 ↓ 1.2 6 1

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

64. 0.027 0.035 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
66. 0.180 0.180 ↓ 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.006..0.006 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
67. 0.011 0.239 ↑ 1.0 1 1

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

68. 0.011 0.228 ↓ 1.5 30 1

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

69. 0.029 0.037 ↑ 2.6 30 1

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

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

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

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
71. 0.180 0.180 ↑ 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.006..0.006 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)