explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1bMk

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,054.215 ↑ 70,770.0 1 1

Unique (cost=220,223.71..220,931.41 rows=70,770 width=377) (actual time=1,054.215..1,054.215 rows=1 loops=1)

2. 0.033 1,054.214 ↑ 70,770.0 1 1

Sort (cost=220,223.71..220,400.63 rows=70,770 width=377) (actual time=1,054.214..1,054.214 rows=1 loops=1)

  • Sort Key: p.m_product_id, a.guaranteedate, s.m_attributesetinstance_id, (COALESCE(p.upc, p.sku)), a.created
  • Sort Method: quicksort Memory: 25kB
3. 3.180 1,054.181 ↑ 70,770.0 1 1

GroupAggregate (cost=161,492.63..202,185.38 rows=70,770 width=377) (actual time=1,054.180..1,054.181 rows=1 loops=1)

4. 0.027 1,051.001 ↑ 70,770.0 1 1

Sort (cost=161,492.63..161,669.56 rows=70,770 width=377) (actual time=1,051.001..1,051.001 rows=1 loops=1)

  • Sort Key: p.m_product_id, s.m_attributesetinstance_id, a.created, a.serno, a.lot, a.guaranteedate, aa.isserno, aa.islot, aa.isguaranteeda
  • Sort Method: quicksort Memory: 25kB
5. 0.032 1,050.974 ↑ 70,770.0 1 1

Hash Left Join (cost=40,417.17..143,454.31 rows=70,770 width=377) (actual time=867.683..1,050.974 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.003 1,050.932 ↑ 70,770.0 1 1

Nested Loop Left Join (cost=40,416.14..141,507.09 rows=70,770 width=283) (actual time=867.642..1,050.932 rows=1 loops=1)

  • Join Filter: ((p.ad_client_id = trl.ad_client_id) AND (p.m_product_id = trl.m_product_id))
7. 310.982 1,018.121 ↑ 70,770.0 1 1

Hash Left Join (cost=40,415.71..127,064.80 rows=70,770 width=255) (actual time=834.832..1,018.121 rows=1 loops=1)

  • Hash Cond: (p.m_attributeset_id = aa.m_attributeset_id)
  • -> Nested Loop Left Join (cost=40414.49..126090.58 rows=70770 width=223) (actual time=834.768..1018.056 rows=1 loops=
  • Join Filter: (p.m_attributeset_id = a.m_attributeset_id)
  • -> Hash Right Join (cost=40414.06..89011.51 rows=70770 width=101) (actual time=834.764..1018.051 rows=1 loops=1
  • Hash Cond: (s.m_product_id = p.m_product_id)
  • Filter: ((lower((p.sku)::text) = '100155513'::text) OR (lower((p.upc)::text) = '100155513'::text) OR ((lowe
  • Rows Removed by Filter: 100659
  • -> Seq Scan on c_uom_conversion c (cost=0.00..1.02 rows=1 width=16) (actual time=0.008..0.008 rows=0 lo
  • Filter: ((isactive = 'Y'::bpchar) AND (lower((upc)::text) = '100155513'::text))
  • Rows Removed by Filter: 1
  • -> Index Scan using m_attributesetinstance_pkey on m_attributesetinstance asi (cost=0.42..8.45 rows=1 w
  • Index Cond: (s.m_attributesetinstance_id = m_attributesetinstance_id)
  • Filter: ((p.m_attributeset_id = m_attributeset_id) AND (((serno)::text = '100155513'::text) OR ((lo
  • Rows Removed by Filter: 0
8. 533.400 533.416 ↓ 2.2 43,729 1

Hash Join (cost=1.35..31,386.59 rows=19,861 width=30) (actual time=0.056..533.416 rows=43,729 loops=1)

  • Hash Cond: (s.m_locator_id = l.m_locator_id)
  • -> Seq Scan on m_storage s (cost=0.00..29175.68 rows=536253 width=20) (actual time=0.020..400.424 r
  • Filter: ((qtyonhand > 0::numeric) OR (m_attributesetinstance_id = 0::numeric))
  • Rows Removed by Filter: 312786
9. 0.016 0.016 ↑ 1.0 1 1

Hash (cost=1.34..1.34 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • -> Seq Scan on m_locator l (cost=0.00..1.34 rows=1 width=32) (actual time=0.009..0.013 rows=1
  • Filter: (m_warehouse_id = 1000001::numeric)
  • Rows Removed by Filter: 30
10. 173.697 173.697 ↓ 1.0 97,691 1

Hash (cost=38,038.19..38,038.19 rows=94,200 width=78) (actual time=173.697..173.697 rows=97,691 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 5745kB
  • -> Bitmap Heap Scan on m_product p (cost=8179.02..38038.19 rows=94200 width=78) (actual time=13.238
  • Recheck Cond: (isactive = 'Y'::bpchar)
  • Filter: ((ad_client_id = 1000001::numeric) AND (ad_org_id = ANY ('{0,2587657,1000002,2587656,25
  • Rows Removed by Filter: 69
  • -> Bitmap Index Scan on m_product_lowerupc (cost=0.00..8155.47 rows=94220 width=0) (actual ti
11. 0.000 0.000 ↓ 0.0

Index Scan using m_attributesetinstance_pkey on m_attributesetinstance a (cost=0.42..0.51 rows=1 width=134) (actual rows= loops=)

  • Index Cond: (s.m_attributesetinstance_id = m_attributesetinstance_id)
12. 0.009 0.026 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
13. 0.017 0.017 ↑ 1.0 10 1

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

14. 32.808 32.808 ↓ 0.0 0 1

Materialize (cost=0.42..13,203.82 rows=1 width=40) (actual time=32.808..32.808 rows=0 loops=1)

  • -> Index Scan using m_product_trl_pkey on m_product_trl trl (cost=0.42..13203.82 rows=1 width=40) (actual time=32.804
  • Index Cond: ((ad_language)::text = 'en_US'::text)
  • Filter: (ad_client_id = 1000001::numeric)
15. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=1.02..1.02 rows=1 width=126) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
16. 0.009 0.009 ↓ 0.0 0 1

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

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