explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fFck

Settings
# exclusive inclusive rows x rows loops node
1. 269.997 5,073.825 ↑ 32.0 1 1

HashAggregate (cost=263,481.38..263,482.42 rows=32 width=151) (actual time=5,073.825..5,073.825 rows=1 loops=1)

  • Group Key: COALESCE(p.upc, p.sku)
2. 1.222 4,803.828 ↓ 1.6 75 1

Merge Right Join (cost=255,381.75..263,376.27 rows=46 width=151) (actual time=4,801.353..4,803.828 rows=75 loops=1)

  • Merge Cond: (ol.m_product_id = p.m_product_id)
3. 97.768 1,722.615 ↑ 2.7 7,598 1

GroupAggregate (cost=141,015.92..146,541.04 rows=20,650 width=16) (actual time=1,529.905..1,722.615 rows=7,598 loops=1)

  • Group Key: ol.m_product_id
4. 445.309 1,624.847 ↑ 2.3 184,451 1

Sort (cost=141,015.92..142,059.00 rows=417,229 width=16) (actual time=1,529.885..1,624.847 rows=184,451 loops=1)

  • Sort Key: ol.m_product_id
  • Sort Method: external merge Disk: 10832kB
5. 454.101 1,179.538 ↑ 1.0 410,811 1

Hash Join (cost=30,115.53..94,933.60 rows=417,229 width=16) (actual time=314.672..1,179.538 rows=410,811 loops=1)

  • Hash Cond: (ol.c_order_id = o.c_order_id)
6. 418.161 418.161 ↓ 1.0 992,398 1

Seq Scan on c_orderline ol (cost=0.00..44,536.47 rows=992,347 width=24) (actual time=0.005..418.161 rows=992,398 loops=1)

7. 52.720 307.276 ↓ 1.2 224,290 1

Hash (cost=26,933.83..26,933.83 rows=193,896 width=8) (actual time=307.276..307.276 rows=224,290 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3262kB
8. 254.556 254.556 ↓ 1.2 224,290 1

Index Scan using c_order_dateordered on c_order o (cost=0.43..26,933.83 rows=193,896 width=8) (actual time=0.085..254.556 rows=224,290 loops=1)

  • Index Cond: ((dateordered >= to_timestamp('2016-01-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateordered <= to_timestamp('2018-12-03 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (issotrx = 'Y'::bpchar) AND (isactive = 'Y'::bpchar))
  • Rows Removed by Filter: 73062
9. 0.056 3,079.991 ↓ 1.6 75 1

Materialize (cost=114,365.83..116,576.84 rows=46 width=87) (actual time=3,079.529..3,079.991 rows=75 loops=1)

10. 0.067 3,079.935 ↓ 1.6 75 1

Nested Loop Left Join (cost=114,365.83..116,576.73 rows=46 width=87) (actual time=3,079.526..3,079.935 rows=75 loops=1)

11. 0.619 3,079.574 ↑ 1.1 42 1

Merge Left Join (cost=114,365.41..114,372.09 rows=46 width=87) (actual time=3,079.505..3,079.574 rows=42 loops=1)

  • Merge Cond: (p.m_product_id = vendorreturn.m_product_id)
12. 0.060 2,893.374 ↑ 1.1 42 1

Sort (cost=70,906.96..70,907.07 rows=46 width=55) (actual time=2,893.361..2,893.374 rows=42 loops=1)

  • Sort Key: p.m_product_id
  • Sort Method: quicksort Memory: 28kB
13. 4.648 2,893.314 ↑ 1.1 42 1

Hash Right Join (cost=70,603.31..70,905.69 rows=46 width=55) (actual time=2,873.901..2,893.314 rows=42 loops=1)

  • Hash Cond: (t.m_product_id = p.m_product_id)
14. 443.729 2,888.439 ↓ 3.5 40,344 1

HashAggregate (cost=70,415.23..70,559.18 rows=11,516 width=11) (actual time=2,873.505..2,888.439 rows=40,344 loops=1)

  • Group Key: t.m_product_id
15. 2,444.710 2,444.710 ↓ 1.2 767,169 1

Seq Scan on m_transaction t (cost=0.00..67,110.72 rows=660,902 width=11) (actual time=294.247..2,444.710 rows=767,169 loops=1)

  • Filter: ((movementdate >= to_timestamp('2016-01-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (movementdate <= to_timestamp('2018-12-03 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (movementtype = ANY ('{I+,I-,V+,M+,M-}'::bpchar[])))
  • Rows Removed by Filter: 772632
16. 0.026 0.227 ↑ 1.1 42 1

Hash (cost=187.50..187.50 rows=46 width=23) (actual time=0.227..0.227 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.093 0.201 ↑ 1.1 42 1

Bitmap Heap Scan on m_product p (cost=9.10..187.50 rows=46 width=23) (actual time=0.139..0.201 rows=42 loops=1)

  • Recheck Cond: ((lower((upc)::text) ~~ 'h61053'::text) OR (lower((sku)::text) ~~ 'h61053'::text))
  • Filter: ((isactive ~~ 'Y'::text) AND (ad_client_id = '1000000'::numeric) AND ((lower((upc)::text) ~~ 'h61053'::text) OR (lower((sku)::text) ~~ 'h61053'::text)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=9
18. 0.001 0.108 ↓ 0.0 0 1

BitmapOr (cost=9.10..9.10 rows=50 width=0) (actual time=0.108..0.108 rows=0 loops=1)

19. 0.060 0.060 ↑ 1.1 43 1

Bitmap Index Scan on m_product_lowerupc (cost=0.00..4.66 rows=49 width=0) (actual time=0.060..0.060 rows=43 loops=1)

  • Index Cond: (lower((upc)::text) = 'h61053'::text)
20. 0.047 0.047 ↓ 0.0 0 1

Bitmap Index Scan on m_product_lowersku (cost=0.00..4.42 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=1)

  • Index Cond: (lower((sku)::text) = 'h61053'::text)
21. 1.754 185.581 ↓ 3.0 3,831 1

Sort (cost=43,458.45..43,461.67 rows=1,289 width=39) (actual time=185.391..185.581 rows=3,831 loops=1)

  • Sort Key: vendorreturn.m_product_id
  • Sort Method: quicksort Memory: 407kB
22. 0.493 183.827 ↓ 3.6 4,579 1

Subquery Scan on vendorreturn (cost=43,362.86..43,391.86 rows=1,289 width=39) (actual time=181.989..183.827 rows=4,579 loops=1)

23. 4.523 183.334 ↓ 3.6 4,579 1

HashAggregate (cost=43,362.86..43,378.97 rows=1,289 width=12) (actual time=181.987..183.334 rows=4,579 loops=1)

  • Group Key: il.m_product_id
24. 1.511 178.811 ↓ 3.9 5,016 1

Nested Loop (cost=0.42..43,356.41 rows=1,289 width=12) (actual time=62.531..178.811 rows=5,016 loops=1)

25. 173.748 173.748 ↑ 1.9 296 1

Seq Scan on m_inout i (cost=0.00..23,670.72 rows=548 width=8) (actual time=62.495..173.748 rows=296 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (movementtype = 'V-'::bpchar) AND (isactive = 'Y'::bpchar) AND (movementdate >= to_timestamp('2016-01-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (movementdate <= to_timestamp('2018-12-03 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 375009
26. 3.552 3.552 ↓ 1.9 17 296

Index Scan using m_inoutline_inout on m_inoutline il (cost=0.42..35.83 rows=9 width=20) (actual time=0.007..0.012 rows=17 loops=296)

  • Index Cond: (m_inout_id = i.m_inout_id)
27. 0.294 0.294 ↑ 2.0 2 42

Index Scan using productpo_product_id on m_product_po po (cost=0.42..47.89 rows=4 width=14) (actual time=0.005..0.007 rows=2 loops=42)

  • Index Cond: (m_product_id = p.m_product_id)
  • Filter: (iscurrentvendor = 'Y'::bpchar)
  • Rows Removed by Filter: 1