explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txAI

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

HashAggregate (cost=30,088,180.55..30,093,881.27 rows=190,024 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,064,732.39..30,077,729.23 rows=190,024 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,897,340.61..21,906,241.88 rows=173,488 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=20,897,340.61..21,233,641.03 rows=134,520,169 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,734,933.69 rows=134,520,169 width=24) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=8,167,391.77..8,167,866.83 rows=190,024 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,043,617.70..8,150,730.64 rows=190,024 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,154,284.74..2,155,072.15 rows=314,966 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,593.79..2,125,520.73 rows=314,966 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,592.59..2,081,227.47 rows=2,725,665 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=221.59..2,108,288.93 rows=489,266 width=40) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=221.59..2,107,065.77 rows=489,266 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 = 1053)
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=221.01..17,637.99 rows=5,415 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..219.66 rows=5,415 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,889,332.96..5,918,549.14 rows=11,686,471 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,283,709.08..4,517,438.50 rows=11,686,471 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,283,709.08..4,400,573.79 rows=11,686,471 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,115,062.04 rows=116,864,704 width=16) (actual rows= loops=)