explain.depesz.com

PostgreSQL's explain analyze made readable

Result: od6Y

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

HashAggregate (cost=31,725,120.22..31,728,686.56 rows=118,878 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=30,704,609.76..31,718,581.93 rows=118,878 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=22,548,713.67..23,559,155.07 rows=173,112 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=22,548,713.67..22,885,527.47 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,155,896.10..8,156,193.29 rows=118,878 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,099,812.45..8,145,875.20 rows=118,878 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=291,017.67..292,206.45 rows=475,511 width=36) (actual rows= loops=)

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

Hash Join (cost=186,108.11..246,179.08 rows=475,511 width=36) (actual rows= loops=)

  • Hash Cond: (products_stores.product_id = products.id)
  • Join Filter: ((stores.nominate_store_id = products_stores.store_id) OR (stores.id IS NULL))
10. 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=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=178,242.81..178,242.81 rows=629,224 width=48) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.34..178,242.81 rows=629,224 width=48) (actual rows= loops=)

  • Hash Cond: (sales.parent_store_id = stores.id)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.14..169,589.78 rows=629,224 width=40) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..608.85 rows=119 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (supplier_id = 1053)
16. 0.000 0.000 ↓ 0.0

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

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

Index Scan using index_only_keys on sales (cost=0.57..1,365.07 rows=5,494 width=28) (actual rows= loops=)

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

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

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

Materialize (cost=7,808,794.78..7,826,198.01 rows=3,480,646 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=7,808,794.78..7,817,496.39 rows=3,480,646 width=12) (actual rows= loops=)

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

Subquery Scan on filtered_sale_id (cost=6,891,071.16..7,395,764.71 rows=3,480,646 width=12) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,891,071.16..7,360,958.25 rows=3,480,646 width=16) (actual rows= loops=)

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

Sort (cost=6,891,071.16..6,978,087.28 rows=34,806,451 width=16) (actual rows= loops=)

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

Index Scan using sales_sale_date_index on sales sales_1 (cost=0.57..2,182,661.24 rows=34,806,451 width=16) (actual rows= loops=)

  • Index Cond: (sale_date >= ((now() - '84 days'::interval))::date)