explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g1yn

Settings
# exclusive inclusive rows x rows loops node
1. 6.134 10,257.703 ↓ 1.4 704 1

Sort (cost=5,411.03..5,412.31 rows=514 width=187) (actual time=10,257.649..10,257.703 rows=704 loops=1)

  • Sort Key: (CASE WHEN (lower((p.name)::text) ~~ 'sta'::text) THEN 1 WHEN (lower((p.name)::text) ~~ 'sta%'::text) THEN 2 ELSE 3 END), p.name, (productattribute(s.m_attributesetinstance_id))
  • Sort Method: quicksort Memory: 145kB
2. 9,834.281 10,251.569 ↓ 1.4 704 1

HashAggregate (cost=5,118.03..5,387.88 rows=514 width=187) (actual time=540.083..10,251.569 rows=704 loops=1)

  • Group Key: p.m_product_id, a.created, a.serno, a.lot, a.guaranteedate, s.m_attributesetinstance_id, l.m_warehouse_id, mas.isserno, mas.islot, mas.isguaranteedate, mas.m_attributeset_id, p.c_uom_id, s.qtyreserved
3. 0.609 417.288 ↓ 1.4 704 1

Hash Left Join (cost=2,200.65..5,100.04 rows=514 width=187) (actual time=246.069..417.288 rows=704 loops=1)

  • Hash Cond: ((p.m_attributeset_id = a.m_attributeset_id) AND (s.m_attributesetinstance_id = a.m_attributesetinstance_id))
4. 0.353 370.264 ↓ 1.4 704 1

Hash Left Join (cost=1,702.10..4,597.63 rows=514 width=151) (actual time=199.594..370.264 rows=704 loops=1)

  • Hash Cond: (p.m_attributeset_id = mas.m_attributeset_id)
5. 1.765 369.721 ↓ 1.4 704 1

Hash Left Join (cost=1,700.87..4,594.45 rows=514 width=111) (actual time=199.386..369.721 rows=704 loops=1)

  • Hash Cond: (p.m_product_id = s.m_product_id)
6. 181.632 181.632 ↓ 1.4 704 1

Seq Scan on m_product p (cost=0.00..2,882.65 rows=514 width=86) (actual time=13.037..181.632 rows=704 loops=1)

  • Filter: ((ad_org_id = ANY ('{0,1000000}'::numeric[])) AND (isactive = 'Y'::bpchar) AND (issold = 'Y'::bpchar) AND (ad_client_id = '1000000'::numeric) AND (lower((name)::text) ~~ '%sta%'::text))
  • Rows Removed by Filter: 48642
7. 20.588 186.324 ↓ 1,814.5 27,217 1

Hash (cost=1,700.68..1,700.68 rows=15 width=32) (actual time=186.324..186.324 rows=27,217 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1666kB
8. 20.314 165.736 ↓ 1,814.5 27,217 1

Nested Loop (cost=0.00..1,700.68 rows=15 width=32) (actual time=29.156..165.736 rows=27,217 loops=1)

  • Join Filter: (l.m_locator_id = s.m_locator_id)
  • Rows Removed by Join Filter: 9358
9. 0.011 18.628 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.38 rows=1 width=32) (actual time=18.620..18.628 rows=1 loops=1)

10. 7.498 7.498 ↑ 1.0 1 1

Seq Scan on m_locator l (cost=0.00..1.19 rows=1 width=32) (actual time=7.494..7.498 rows=1 loops=1)

  • Filter: (m_warehouse_id = '1000000'::numeric)
  • Rows Removed by Filter: 14
11. 11.119 11.119 ↑ 1.0 1 1

Seq Scan on m_warehouse w (cost=0.00..1.19 rows=1 width=16) (actual time=11.116..11.119 rows=1 loops=1)

  • Filter: (m_warehouse_id = '1000000'::numeric)
  • Rows Removed by Filter: 14
12. 126.794 126.794 ↓ 166.2 36,575 1

Seq Scan on m_storage s (cost=0.00..1,695.55 rows=220 width=21) (actual time=0.007..126.794 rows=36,575 loops=1)

  • Filter: ((qtyonhand > '0'::numeric) OR (COALESCE(m_attributesetinstance_id, '0'::numeric) = '0'::numeric))
  • Rows Removed by Filter: 3395
13. 0.012 0.190 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=40) (actual time=0.190..0.190 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.178 0.178 ↑ 1.0 10 1

Seq Scan on m_attributeset mas (cost=0.00..1.10 rows=10 width=40) (actual time=0.172..0.178 rows=10 loops=1)

15. 11.519 46.415 ↑ 1.0 12,182 1

Hash (cost=315.82..315.82 rows=12,182 width=48) (actual time=46.415..46.415 rows=12,182 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1023kB
16. 34.896 34.896 ↑ 1.0 12,182 1

Seq Scan on m_attributesetinstance a (cost=0.00..315.82 rows=12,182 width=48) (actual time=0.007..34.896 rows=12,182 loops=1)