explain.depesz.com

PostgreSQL's explain analyze made readable

Result: twqh

Settings
# exclusive inclusive rows x rows loops node
1. 55.294 26,551.929 ↓ 1.5 10,722 1

Sort (cost=182,262.64..182,280.41 rows=7,109 width=381) (actual time=26,550.490..26,551.929 rows=10,722 loops=1)

  • Sort Key: p.name, p.isbaseproduct DESC
  • Sort Method: quicksort Memory: 3,761kB
2. 22,713.773 26,496.518 ↓ 1.5 10,722 1

Hash Left Join (cost=94,485.71..181,807.82 rows=7,109 width=381) (actual time=3,583.609..26,496.518 rows=10,722 loops=1)

  • Hash Cond: (p.m_product_category_id = pc.m_product_category_id)
3. 20.043 3,782.745 ↓ 1.5 10,722 1

Hash Join (cost=94,467.94..178,137.80 rows=7,109 width=366) (actual time=3,568.871..3,782.745 rows=10,722 loops=1)

  • Hash Cond: (p.c_uom_id = u.c_uom_id)
4. 128.949 3,762.702 ↓ 1.5 10,722 1

Hash Right Join (cost=94,466.56..178,038.67 rows=7,109 width=228) (actual time=3,568.853..3,762.702 rows=10,722 loops=1)

  • Hash Cond: (s.m_product_id = p.m_product_id)
  • Filter: ((GREATEST((max(s.updated)), p.updated) >= to_timestamp('1970-01-19 13:12:21'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) OR (SubPlan 1))
5. 1,986.330 3,214.818 ↓ 1.7 80,928 1

HashAggregate (cost=66,489.35..67,078.43 rows=47,126 width=20) (actual time=3,149.885..3,214.818 rows=80,928 loops=1)

  • Group Key: s.m_product_id
6. 783.565 1,228.488 ↓ 2.3 1,654,712 1

Hash Join (cost=12.94..59,407.35 rows=708,200 width=20) (actual time=0.213..1,228.488 rows=1,654,712 loops=1)

  • Hash Cond: (s.m_locator_id = l.m_locator_id)
7. 444.731 444.731 ↓ 1.0 1,654,759 1

Seq Scan on m_storage s (cost=0.00..46,115.66 rows=1,652,466 width=26) (actual time=0.004..444.731 rows=1,654,759 loops=1)

8. 0.017 0.192 ↓ 2.0 84 1

Hash (cost=12.42..12.42 rows=42 width=16) (actual time=0.192..0.192 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
9. 0.028 0.175 ↓ 2.0 84 1

Hash Join (cost=8.65..12.42 rows=42 width=16) (actual time=0.142..0.175 rows=84 loops=1)

  • Hash Cond: (l.m_warehouse_id = w.m_warehouse_id)
10. 0.017 0.017 ↓ 1.0 99 1

Seq Scan on m_locator l (cost=0.00..2.98 rows=98 width=32) (actual time=0.003..0.017 rows=99 loops=1)

11. 0.012 0.130 ↓ 1.9 79 1

Hash (cost=8.12..8.12 rows=42 width=16) (actual time=0.130..0.130 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
12. 0.118 0.118 ↓ 1.9 79 1

Seq Scan on m_warehouse w (cost=0.00..8.12 rows=42 width=16) (actual time=0.027..0.118 rows=79 loops=1)

  • Filter: (ad_org_id = ANY ('{0,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000006,1000007,1000008,1000009,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000033,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,0}'::numeric[]))
  • Rows Removed by Filter: 15
13. 55.434 418.935 ↓ 1.0 10,722 1

Hash (cost=27,843.92..27,843.92 rows=10,663 width=204) (actual time=418.935..418.935 rows=10,722 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,547kB
14. 363.501 363.501 ↓ 1.0 10,722 1

Seq Scan on m_product p (cost=0.00..27,843.92 rows=10,663 width=204) (actual time=0.012..363.501 rows=10,722 loops=1)

  • Filter: ((ad_client_id = '1000000'::numeric) AND (iswebstorefeatured = 'Y'::bpchar))
  • Rows Removed by Filter: 218,514
15.          

SubPlan (for Sort)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..365.71 rows=10 width=0) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using m_productprice_pkey on m_productprice pp (cost=0.56..165.31 rows=40 width=7) (never executed)

  • Index Cond: (m_product_id = p.m_product_id)
  • Filter: (updated >= to_timestamp('1970-01-19 13:12:21'::text, 'YYYY-MM-DD HH24:MI:SS'::text))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using m_pricelist_version_pkey on m_pricelist_version plv (cost=0.28..5.00 rows=1 width=7) (never executed)

  • Index Cond: (m_pricelist_version_id = pp.m_pricelist_version_id)
  • Filter: (m_pricelist_id = '1000000'::numeric)
19. 0.004 0.012 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=154) (actual time=0.012..0.012 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.008 0.008 ↑ 1.0 17 1

Seq Scan on c_uom u (cost=0.00..1.17 rows=17 width=154) (actual time=0.004..0.008 rows=17 loops=1)

21. 0.046 0.105 ↑ 1.4 270 1

Hash (cost=12.90..12.90 rows=390 width=21) (actual time=0.105..0.105 rows=270 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
22. 0.059 0.059 ↑ 1.4 270 1

Seq Scan on m_product_category pc (cost=0.00..12.90 rows=390 width=21) (actual time=0.005..0.059 rows=270 loops=1)