explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RvOC : Optimization for: plan #LcfU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,052.740 205,939.116 ↑ 1.6 19,439 1

GroupAggregate (cost=3,601,367.96..39,836,907.88 rows=30,476 width=135) (actual time=98,953.214..205,939.116 rows=19,439 loops=1)

  • Group Key: m_product.m_product_id, c_invoice.dateinvoiced, c_invoiceline.ad_client_id, m_product.m_product_category_id, gen.m_product_id
2. 208.435 98,982.704 ↓ 1.0 31,295 1

Sort (cost=3,601,367.96..3,601,444.15 rows=30,476 width=135) (actual time=98,952.319..98,982.704 rows=31,295 loops=1)

  • Sort Key: m_product.m_product_id, c_invoice.dateinvoiced, c_invoiceline.ad_client_id, m_product.m_product_category_id, gen.m_product_id
  • Sort Method: external sort Disk: 4240kB
3. 97.043 98,774.269 ↓ 1.0 31,295 1

Merge Right Join (cost=1.85..3,597,012.20 rows=30,476 width=135) (actual time=4.484..98,774.269 rows=31,295 loops=1)

  • Merge Cond: ((m_product.m_product_id)::text = (c_invoiceline.m_product_id)::text)
  • Filter: (COALESCE((SubPlan 12), '0'::numeric) <> '0'::numeric)
  • Rows Removed by Filter: 917
4. 15.509 29.454 ↑ 1.0 19,751 1

Nested Loop Left Join (cost=0.57..13,773.11 rows=19,751 width=88) (actual time=0.018..29.454 rows=19,751 loops=1)

5. 13.945 13.945 ↑ 1.0 19,751 1

Index Scan using m_product_key on m_product (cost=0.29..3,767.55 rows=19,751 width=161) (actual time=0.011..13.945 rows=19,751 loops=1)

6. 0.000 0.000 ↓ 0.0 0 19,751

Index Only Scan using m_product_key on m_product gen (cost=0.29..0.50 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=19,751)

  • Index Cond: (m_product_id = (m_product.generic_product_id)::text)
  • Heap Fetches: 0
7. 19.620 304.536 ↓ 1.1 32,212 1

Materialize (cost=0.57..23,882.25 rows=30,629 width=56) (actual time=0.064..304.536 rows=32,212 loops=1)

8. 55.894 284.916 ↓ 1.1 32,212 1

Nested Loop (cost=0.57..23,805.68 rows=30,629 width=56) (actual time=0.060..284.916 rows=32,212 loops=1)

9. 37.582 37.582 ↑ 1.0 38,288 1

Index Scan using c_invoiceline_product on c_invoiceline (cost=0.29..8,589.24 rows=38,288 width=77) (actual time=0.007..37.582 rows=38,288 loops=1)

10. 191.440 191.440 ↑ 1.0 1 38,288

Index Scan using c_invoice_key on c_invoice (cost=0.29..0.39 rows=1 width=38) (actual time=0.005..0.005 rows=1 loops=38,288)

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

SubPlan (forMerge Right Join)

12. 161.060 98,343.236 ↑ 1.0 1 32,212

Aggregate (cost=117.95..117.96 rows=1 width=5) (actual time=3.053..3.053 rows=1 loops=32,212)

13. 62,180.928 98,182.176 ↓ 5.0 5 32,212

Nested Loop Left Join (cost=4.64..117.94 rows=1 width=5) (actual time=1.222..3.048 rows=5 loops=32,212)

  • Filter: (trunc(inv_10.dateinvoiced) = trunc(c_invoice.dateinvoiced))
  • Rows Removed by Filter: 215
14. 6,828.944 7,730.880 ↓ 24.3 219 32,212

Bitmap Heap Scan on c_invoiceline invline_10 (cost=4.36..38.61 rows=9 width=34) (actual time=0.044..0.240 rows=219 loops=32,212)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=5154960
15. 901.936 901.936 ↓ 24.3 219 32,212

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.028..0.028 rows=219 loops=32,212)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
16. 28,270.368 28,270.368 ↑ 1.0 1 7,067,592

Index Scan using c_invoice_key on c_invoice inv_10 (cost=0.29..8.30 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=7,067,592)

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

SubPlan (forGroupAggregate)

18. 19.439 136.073 ↓ 0.0 0 19,439

Limit (cost=8.17..8.18 rows=1 width=90) (actual time=0.007..0.007 rows=0 loops=19,439)

19. 77.756 116.634 ↓ 0.0 0 19,439

Sort (cost=8.17..8.18 rows=1 width=90) (actual time=0.006..0.006 rows=0 loops=19,439)

  • Sort Key: m_product_po.created DESC
  • Sort Method: quicksort Memory: 25kB
20. 38.878 38.878 ↓ 0.0 0 19,439

Index Scan using m_prod_po_product_idx on m_product_po (cost=0.14..8.16 rows=1 width=90) (actual time=0.002..0.002 rows=0 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Filter: (iscurrentvendor = 'Y'::bpchar)
21. 38.878 9,155.769 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.471..0.471 rows=1 loops=19,439)

22. 212.900 9,116.891 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.469..0.469 rows=0 loops=19,439)

23. 1,419.047 1,730.071 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline (cost=4.36..38.61 rows=9 width=34) (actual time=0.025..0.089 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
24. 311.024 311.024 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.016..0.016 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
25. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '5D7F804DE3814CDAABAB2CB68F5C474D'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
26. 19.439 8,844.745 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.455..0.455 rows=1 loops=19,439)

27. 57.388 8,825.306 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.454..0.454 rows=0 loops=19,439)

28. 1,302.413 1,593.998 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_1 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.082 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
29. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
30. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_1 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_1.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '5F143329E05F44659DD584AD39BB0F04'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
31. 19.439 8,805.867 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.453..0.453 rows=1 loops=19,439)

32. 37.949 8,786.428 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.452..0.452 rows=0 loops=19,439)

33. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_2 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
34. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
35. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_2 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_2.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '4B1D21E40C3A4A91A6F4D70E1528FDAF'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
36. 19.439 8,786.428 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.452..0.452 rows=1 loops=19,439)

37. 18.510 8,766.989 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.451..0.451 rows=0 loops=19,439)

38. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_3 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
39. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
40. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_3 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_3.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '69C70CBA08B7441C9573CE8C014BDAA3'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
41. 19.439 8,805.867 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.453..0.453 rows=1 loops=19,439)

42. 37.949 8,786.428 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.452..0.452 rows=0 loops=19,439)

43. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_4 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
44. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
45. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_4 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_4.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = 'EB49330C8CB94BCBB509CFBC59C52B6C'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
46. 19.439 8,805.867 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.453..0.453 rows=1 loops=19,439)

47. 37.949 8,786.428 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.452..0.452 rows=0 loops=19,439)

48. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_5 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
49. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
50. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_5 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_5.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '34DAD183BBDE4F319D7988210EB0D7BD'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
51. 19.439 8,805.867 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.453..0.453 rows=1 loops=19,439)

52. 37.949 8,786.428 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.452..0.452 rows=0 loops=19,439)

53. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_6 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
54. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
55. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_6 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_6.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = 'CAE21A8CCB274F10A471C92FC369A45F'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
56. 19.439 8,766.989 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.451..0.451 rows=1 loops=19,439)

57. 0.000 8,747.550 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.450..0.450 rows=0 loops=19,439)

58. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_7 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
59. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
60. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_7 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_7.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '01D7CCD4B85648C7968EAAFE17833306'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
61. 19.439 8,786.428 ↑ 1.0 1 19,439

Aggregate (cost=117.97..117.98 rows=1 width=5) (actual time=0.452..0.452 rows=1 loops=19,439)

62. 18.510 8,766.989 ↓ 0.0 0 19,439

Nested Loop (cost=4.64..117.96 rows=1 width=5) (actual time=0.451..0.451 rows=0 loops=19,439)

63. 1,282.974 1,574.559 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_8 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.081 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
64. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
65. 7,173.920 7,173.920 ↓ 0.0 0 1,793,480

Index Scan using c_invoice_key on c_invoice inv_8 (cost=0.29..8.81 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_8.c_invoice_id)::text)
  • Filter: (((ad_org_id)::text = '615CBF002A4A447D89F4359D4615B074'::text) AND (trunc(dateinvoiced) = trunc(c_invoice.dateinvoiced)))
  • Rows Removed by Filter: 1
66. 58.317 26,203.772 ↑ 1.0 1 19,439

Aggregate (cost=117.95..117.96 rows=1 width=5) (actual time=1.348..1.348 rows=1 loops=19,439)

67. 16,930.440 26,145.455 ↓ 2.0 2 19,439

Nested Loop Left Join (cost=4.64..117.94 rows=1 width=5) (actual time=0.647..1.345 rows=2 loops=19,439)

  • Filter: (trunc(inv_9.dateinvoiced) = trunc(c_invoice.dateinvoiced))
  • Rows Removed by Filter: 91
68. 1,749.510 2,041.095 ↓ 10.2 92 19,439

Bitmap Heap Scan on c_invoiceline invline_9 (cost=4.36..38.61 rows=9 width=34) (actual time=0.023..0.105 rows=92 loops=19,439)

  • Recheck Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
  • Heap Blocks: exact=1416190
69. 291.585 291.585 ↓ 10.2 92 19,439

Bitmap Index Scan on c_invoiceline_product (cost=0.00..4.36 rows=9 width=0) (actual time=0.015..0.015 rows=92 loops=19,439)

  • Index Cond: ((m_product_id)::text = (m_product.m_product_id)::text)
70. 7,173.920 7,173.920 ↑ 1.0 1 1,793,480

Index Scan using c_invoice_key on c_invoice inv_9 (cost=0.29..8.30 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=1,793,480)

  • Index Cond: ((c_invoice_id)::text = (invline_9.c_invoice_id)::text)