explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Ek0 : Test 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

HashAggregate (cost=30,244,583.32..30,253,204.60 rows=287,376 width=32) (actual rows= loops=)

  • Group Key: products_stores.store_id, products_stores.product_id, products.category_id, product_stock.soh, product_stock.soo
2. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=29,213,481.51..30,228,777.64 rows=287,376 width=32) (actual rows= loops=)

  • Merge Cond: ((product_stock.store_id = products_stores.store_id) AND (product_stock.product_id = products_stores.product_id))
3. 0.000 0.000 ↓ 0.0

Unique (cost=20,930,427.27..21,940,868.67 rows=173,112 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=20,930,427.27..21,267,241.07 rows=134,725,520 width=24) (actual rows= loops=)

  • Sort Key: product_stock.store_id, product_stock.product_id, product_stock.created_at DESC
5. 0.000 0.000 ↓ 0.0

Seq Scan on product_stock (cost=0.00..2,738,812.20 rows=134,725,520 width=24) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=8,283,054.25..8,283,772.69 rows=287,376 width=24) (actual rows= loops=)

  • Sort Key: products_stores.store_id, products_stores.product_id
7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8,139,750.93..8,256,999.91 rows=287,376 width=24) (actual rows= loops=)

  • Merge Cond: ((products_stores.product_id = filtered_sale_id.product_id) AND (sales.parent_store_id = filtered_sale_id.parent_store_id))
  • Filter: (CASE WHEN (filtered_sale_id.sale_id IS NULL) THEN (products_stores.product_id IS NOT NULL) ELSE (sales.id = filtered_sale_id.sale_id) END AND CASE WHEN (filtered_sale_id.sale_id IS NULL) THEN (products_stores.product_id IS NOT NULL) ELSE (sales.sale_date >= ((now() - '84 days'::interval))::date) END)
8. 0.000 0.000 ↓ 0.0

Sort (cost=2,242,554.52..2,243,743.02 rows=475,399 width=36) (actual rows= loops=)

  • Sort Key: products_stores.product_id, sales.parent_store_id
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=16,598.43..2,197,727.30 rows=475,399 width=36) (actual rows= loops=)

  • Hash Cond: (sales.parent_store_id = stores.id)
  • Filter: ((stores.nominate_store_id = products_stores.store_id) OR (stores.id IS NULL))
10. 0.000 0.000 ↓ 0.0

Merge Join (cost=16,597.23..2,130,873.06 rows=4,114,033 width=36) (actual rows= loops=)

  • Merge Cond: (products_stores.product_id = products.id)
11. 0.000 0.000 ↓ 0.0

Sort (cost=16,371.00..16,766.87 rows=158,348 width=8) (actual rows= loops=)

  • Sort Key: products_stores.product_id
12. 0.000 0.000 ↓ 0.0

Seq Scan on products_stores (cost=0.00..2,695.48 rows=158,348 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Materialize (cost=226.23..2,138,683.58 rows=629,077 width=40) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=226.23..2,137,110.89 rows=629,077 width=40) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,701.25 rows=119 width=12) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Only Scan using product_suppliers_product_id_supplier_id_index on product_suppliers (cost=0.29..799.77 rows=119 width=4) (actual rows= loops=)

  • Index Cond: (supplier_id = 1,053)
17. 0.000 0.000 ↓ 0.0

Index Scan using products_pkey on products (cost=0.29..7.57 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = product_suppliers.product_id)
18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on sales (cost=225.65..17,889.69 rows=5,493 width=28) (actual rows= loops=)

  • Recheck Cond: (product_id = products.id)
  • Filter: (company_id = 1)
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_only_keys (cost=0.00..224.28 rows=5,493 width=0) (actual rows= loops=)

  • Index Cond: (product_id = products.id)
20. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on stores (cost=0.00..1.09 rows=9 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=5,897,196.41..5,926,469.63 rows=11,709,289 width=12) (actual rows= loops=)

  • Sort Key: filtered_sale_id.product_id, filtered_sale_id.parent_store_id
23. 0.000 0.000 ↓ 0.0

Subquery Scan on filtered_sale_id (cost=4,288,272.76..4,522,458.54 rows=11,709,289 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,288,272.76..4,405,365.65 rows=11,709,289 width=16) (actual rows= loops=)

  • Group Key: sales_1.sale_date, sales_1.product_id, sales_1.parent_store_id
25. 0.000 0.000 ↓ 0.0

Seq Scan on sales sales_1 (cost=0.00..3,117,343.88 rows=117,092,888 width=16) (actual rows= loops=)