explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nwoC

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 113.777 ↑ 13,712.0 1 1

Unique (cost=22,109.20..22,246.32 rows=13,712 width=324) (actual time=113.777..113.777 rows=1 loops=1)

2. 0.007 113.776 ↑ 13,712.0 1 1

Sort (cost=22,109.20..22,143.48 rows=13,712 width=324) (actual time=113.776..113.776 rows=1 loops=1)

  • Sort Key: p.m_product_id, a.guaranteedate, s.m_attributesetinstance_id, (COALESCE(p.upc, p.sku)) DESC, a.created
  • Sort Method: quicksort Memory: 25kB
3. 0.787 113.769 ↑ 13,712.0 1 1

GroupAggregate (cost=11,666.49..19,927.97 rows=13,712 width=324) (actual time=113.769..113.769 rows=1 loops=1)

  • Group Key: p.m_product_id, p.name, p.sku, p.upc, p.description, p.upctype, s.m_attributesetinstance_id, a.created, a.serno, a.lot, a.guaranteedate, aa.isserno, aa.islot, aa.isguaranteedate,
4. 0.014 112.982 ↑ 13,712.0 1 1

Sort (cost=11,666.49..11,700.77 rows=13,712 width=324) (actual time=112.982..112.982 rows=1 loops=1)

  • Sort Key: p.m_product_id, p.name, p.sku, p.upc, p.description, p.upctype, s.m_attributesetinstance_id, a.created, a.serno, a.lot, a.guaranteedate, aa.isserno, aa.islot, aa.isguarantee
  • Sort Method: quicksort Memory: 25kB
5. 0.010 112.968 ↑ 13,712.0 1 1

Hash Left Join (cost=5,772.65..9,485.26 rows=13,712 width=324) (actual time=79.557..112.968 rows=1 loops=1)

  • Hash Cond: ((p.m_product_id = conv.m_product_id) AND (p.c_uom_id = conv.c_uom_id))
6. 0.004 112.950 ↑ 13,712.0 1 1

Hash Left Join (cost=5,771.60..9,107.12 rows=13,712 width=230) (actual time=79.539..112.950 rows=1 loops=1)

  • Hash Cond: ((p.ad_client_id = trl.ad_client_id) AND (p.m_product_id = trl.m_product_id))
7. 0.005 108.578 ↑ 13,712.0 1 1

Hash Left Join (cost=4,746.49..7,979.16 rows=13,712 width=188) (actual time=75.168..108.578 rows=1 loops=1)

  • Hash Cond: (p.m_attributeset_id = aa.m_attributeset_id)
8. 0.016 108.566 ↑ 13,712.0 1 1

Hash Left Join (cost=4,745.27..7,926.17 rows=13,712 width=156) (actual time=75.157..108.566 rows=1 loops=1)

  • Hash Cond: ((p.m_attributeset_id = a.m_attributeset_id) AND (s.m_attributesetinstance_id = a.m_attributesetinstance_id))
9. 48.582 103.836 ↑ 13,712.0 1 1

Hash Right Join (cost=4,243.89..7,321.88 rows=13,712 width=120) (actual time=70.427..103.836 rows=1 loops=1)

  • Hash Cond: (s.m_product_id = p.m_product_id)
  • Filter: ((lower((p.sku)::text) = '15264'::text) OR (lower((p.upc)::text) = '15264'::text) OR (hashed SubPlan 1) OR (SubPlan 2))
  • Rows Removed by Filter: 18573
  • -> Index Scan using m_attributesetinstance_pkey on m_attributesetinstance asi (cost=0.29..2.71 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops
  • Index Cond: (s.m_attributesetinstance_id = m_attributesetinstance_id)
  • Filter: ((p.m_attributeset_id = m_attributeset_id) AND (((serno)::text = '15264'::text) OR ((lot)::text = '15264'::text)))
  • Rows Removed by Filter: 1
10. 3.122 22.527 ↓ 8.7 31,592 1

Nested Loop (cost=0.41..1,876.28 rows=3,625 width=30) (actual time=0.034..22.527 rows=31,592 loops=1)

11. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on m_locator l (cost=0.00..1.20 rows=1 width=32) (actual time=0.010..0.012 rows=1 loops=1)

  • Filter: (m_warehouse_id = '1000000'::numeric)
  • Rows Removed by Filter: 15
12. 19.393 19.393 ↓ 3.3 31,592 1

Index Scan using m_storage_pkey on m_storage s (cost=0.41..1,778.41 rows=9,667 width=19) (actual time=0.020..19.393 rows=31,592 loops=1)

  • Index Cond: (m_locator_id = l.m_locator_id)
  • Filter: ((qtyonhand > '0'::numeric) OR (m_attributesetinstance_id = '0'::numeric))
  • Rows Removed by Filter: 2765
13. 6.615 32.722 ↑ 1.0 17,870 1

Hash (cost=4,014.67..4,014.67 rows=18,222 width=97) (actual time=32.722..32.722 rows=17,870 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2253kB
14. 23.120 26.107 ↑ 1.0 17,870 1

Bitmap Heap Scan on m_product p (cost=347.86..4,014.67 rows=18,222 width=97) (actual time=3.183..26.107 rows=17,870 loops=1)

  • Recheck Cond: (isactive = 'Y'::bpchar)
  • Filter: ((ad_client_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,0,1000000}'::numeric[])))
  • Rows Removed by Filter: 23373
  • Heap Blocks: exact=1378
15. 2.987 2.987 ↓ 1.0 41,243 1

Bitmap Index Scan on m_product_lowersku (cost=0.00..343.31 rows=41,003 width=0) (actual time=2.987..2.987 rows=41,243 loops=1)

16.          

SubPlan (forHash Right Join)

17. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on c_uom_conversion c (cost=0.00..1.03 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (lower((upc)::text) = '15264'::text))
  • Rows Removed by Filter: 2
18. 2.543 4.714 ↑ 1.0 12,255 1

Hash (cost=317.55..317.55 rows=12,255 width=48) (actual time=4.714..4.714 rows=12,255 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1026kB
19. 2.171 2.171 ↑ 1.0 12,255 1

Seq Scan on m_attributesetinstance a (cost=0.00..317.55 rows=12,255 width=48) (actual time=0.002..2.171 rows=12,255 loops=1)

20. 0.002 0.007 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=48) (actual time=0.007..0.007 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on m_attributeset aa (cost=0.00..1.10 rows=10 width=48) (actual time=0.002..0.005 rows=10 loops=1)

22. 0.000 4.368 ↓ 0.0 0 1

Hash (cost=1,025.09..1,025.09 rows=1 width=52) (actual time=4.368..4.368 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 4.368 4.368 ↓ 0.0 0 1

Index Scan using m_product_trl_pkey on m_product_trl trl (cost=0.42..1,025.09 rows=1 width=52) (actual time=4.368..4.368 rows=0 loops=1)

  • Index Cond: ((ad_language)::text = 'en_US'::text)
  • Filter: (ad_client_id = '1000000'::numeric)
24. 0.000 0.008 ↓ 0.0 0 1

Hash (cost=1.03..1.03 rows=1 width=126) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on c_uom_conversion conv (cost=0.00..1.03 rows=1 width=126) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND (lower((upc)::text) = '15264'::text))
  • Rows Removed by Filter: 2