explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pLSu : Optimization for: plan #f9dx

Settings

Optimization path:

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

GroupAggregate (cost=435,744,190.61..465,120,982.14 rows=9,668,900 width=102) (actual rows= loops=)

  • Group Key: stock_warehouse.id, product_product.id, product_category.name
2.          

CTE top_parent

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..70,762.89 rows=1,482,471 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on stock_location sl (cost=0.00..127.56 rows=1,811 width=8) (actual rows= loops=)

  • Filter: ((usage)::text = 'internal'::text)
5. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,868.55..4,098.59 rows=148,066 width=8) (actual rows= loops=)

  • Merge Cond: (sl_1.location_id = tp.loc_id)
6. 0.000 0.000 ↓ 0.0

Sort (cost=225.56..230.09 rows=1,811 width=8) (actual rows= loops=)

  • Sort Key: sl_1.location_id
7. 0.000 0.000 ↓ 0.0

Seq Scan on stock_location sl_1 (cost=0.00..127.56 rows=1,811 width=8) (actual rows= loops=)

  • Filter: ((usage)::text = 'internal'::text)
8. 0.000 0.000 ↓ 0.0

Sort (cost=1,642.98..1,688.26 rows=18,110 width=8) (actual rows= loops=)

  • Sort Key: tp.loc_id
9. 0.000 0.000 ↓ 0.0

WorkTable Scan on top_parent tp (cost=0.00..362.20 rows=18,110 width=8) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=435,673,427.72..438,910,657.61 rows=1,294,891,957 width=70) (actual rows= loops=)

  • Sort Key: stock_warehouse.id, product_product.id, product_category.name
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5,383,100.18..27,247,110.96 rows=1,294,891,957 width=70) (actual rows= loops=)

  • Hash Cond: (top_parent.loc_id = stock_inventory.location_id)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,382,863.35..25,757,123.16 rows=555,747,621 width=66) (actual rows= loops=)

  • Hash Cond: (product_template.categ_id = product_category.id)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5,382,854.45..24,250,348.91 rows=555,747,621 width=40) (actual rows= loops=)

  • Hash Cond: ((top_parent.loc_id = stock_move.location_id) AND (product_product.id = stock_move.product_id))
14. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,053,945.56..9,403,615.83 rows=555,747,621 width=32) (actual rows= loops=)

  • Merge Cond: (top_parent.loc_id = stock_quant.location_id)
15. 0.000 0.000 ↓ 0.0

Sort (cost=38,392.99..38,485.64 rows=37,062 width=8) (actual rows= loops=)

  • Sort Key: top_parent.loc_id
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.11..35,580.42 rows=37,062 width=8) (actual rows= loops=)

  • Hash Cond: (top_parent.top_parent_id = stock_warehouse.lot_stock_id)
17. 0.000 0.000 ↓ 0.0

CTE Scan on top_parent (cost=0.00..29,649.42 rows=1,482,471 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on stock_warehouse (cost=0.00..1.05 rows=5 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=1,015,552.57..1,042,093.86 rows=5,308,258 width=28) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=1,015,552.57..1,028,823.22 rows=5,308,258 width=28) (actual rows= loops=)

  • Sort Key: stock_quant.location_id
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,516.78..168,616.27 rows=5,308,258 width=28) (actual rows= loops=)

  • Hash Cond: (product_product.product_tmpl_id = product_template.id)
23. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,298.89..152,460.43 rows=5,308,258 width=28) (actual rows= loops=)

  • Hash Cond: (stock_quant.product_id = product_product.id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on stock_quant (cost=0.00..137,223.58 rows=5,308,258 width=24) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=1,103.95..1,103.95 rows=15,595 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on product_product (cost=0.00..1,103.95 rows=15,595 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=2,022.95..2,022.95 rows=15,595 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on product_template (cost=0.00..2,022.95 rows=15,595 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=4,320,631.99..4,320,631.99 rows=416,260 width=16) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Subquery Scan on stock_move (cost=4,037,367.90..4,320,631.99 rows=416,260 width=16) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=4,037,367.90..4,316,469.39 rows=416,260 width=16) (actual rows= loops=)

  • Group Key: m.location_id, m.product_id
32. 0.000 0.000 ↓ 0.0

Gather Merge (cost=4,037,367.90..4,306,062.89 rows=832,520 width=16) (actual rows= loops=)

  • Workers Planned: 2
33. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=4,036,367.88..4,208,969.40 rows=416,260 width=16) (actual rows= loops=)

  • Group Key: m.location_id, m.product_id
34. 0.000 0.000 ↓ 0.0

Sort (cost=4,036,367.88..4,078,477.61 rows=16,843,892 width=16) (actual rows= loops=)

  • Sort Key: m.location_id, m.product_id
35. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on stock_move m (cost=0.00..1,438,896.92 rows=16,843,892 width=16) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=7.29..7.29 rows=129 width=34) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on product_category (cost=0.00..7.29 rows=129 width=34) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=231.01..231.01 rows=466 width=12) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Subquery Scan on stock_inventory (cost=221.69..231.01 rows=466 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

HashAggregate (cost=221.69..226.35 rows=466 width=12) (actual rows= loops=)

  • Group Key: s.location_id
41. 0.000 0.000 ↓ 0.0

Seq Scan on stock_inventory s (cost=0.00..188.46 rows=6,646 width=12) (actual rows= loops=)