explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8x1N : Optimization for: Optimization for: plan #f9dx; plan #pEXz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 45.962 22,890.417 ↑ 1,506.1 6,420 1

GroupAggregate (cost=435,744,190.61..465,120,982.14 rows=9,668,900 width=102) (actual time=22,814.659..22,890.417 rows=6,420 loops=1)

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

CTE top_parent

3. 3.002 15.319 ↑ 331.6 4,470 1

Recursive Union (cost=0.00..70,762.89 rows=1,482,471 width=8) (actual time=0.059..15.319 rows=4,470 loops=1)

4. 1.453 1.453 ↑ 1.0 1,811 1

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

  • Filter: ((usage)::text = 'internal'::text)
  • Rows Removed by Filter: 74
5. 2.072 10.864 ↑ 222.7 665 4

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

  • Merge Cond: (sl_1.location_id = tp.loc_id)
6. 2.488 5.836 ↑ 1.2 1,503 4

Sort (cost=225.56..230.09 rows=1,811 width=8) (actual time=1.323..1.459 rows=1,503 loops=4)

  • Sort Key: sl_1.location_id
  • Sort Method: quicksort Memory: 133kB
7. 3.348 3.348 ↑ 1.0 1,811 4

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

  • Filter: ((usage)::text = 'internal'::text)
  • Rows Removed by Filter: 74
8. 2.192 2.956 ↑ 10.9 1,668 4

Sort (cost=1,642.98..1,688.26 rows=18,110 width=8) (actual time=0.579..0.739 rows=1,668 loops=4)

  • Sort Key: tp.loc_id
  • Sort Method: quicksort Memory: 27kB
9. 0.764 0.764 ↑ 16.2 1,118 4

WorkTable Scan on top_parent tp (cost=0.00..362.20 rows=18,110 width=8) (actual time=0.001..0.191 rows=1,118 loops=4)

10. 132.578 22,844.455 ↑ 6,174.5 209,716 1

Sort (cost=435,673,427.72..438,910,657.61 rows=1,294,891,957 width=70) (actual time=22,814.648..22,844.455 rows=209,716 loops=1)

  • Sort Key: stock_warehouse.id, product_product.id, product_category.name
  • Sort Method: external merge Disk: 15944kB
11. 29.460 22,711.877 ↑ 6,174.5 209,716 1

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

  • Hash Cond: (top_parent.loc_id = stock_inventory.location_id)
12. 32.442 22,680.517 ↑ 2,650.0 209,716 1

Hash Join (cost=5,382,863.35..25,757,123.16 rows=555,747,621 width=66) (actual time=22,373.596..22,680.517 rows=209,716 loops=1)

  • Hash Cond: (product_template.categ_id = product_category.id)
13. 143.816 22,647.911 ↑ 2,650.0 209,716 1

Hash Left Join (cost=5,382,854.45..24,250,348.91 rows=555,747,621 width=40) (actual time=22,373.422..22,647.911 rows=209,716 loops=1)

  • Hash Cond: ((top_parent.loc_id = max_stock_move.location_id) AND (product_product.id = max_stock_move.product_id))
14. 423.461 5,561.269 ↑ 2,650.0 209,716 1

Merge Join (cost=1,053,945.56..9,403,615.83 rows=555,747,621 width=32) (actual time=5,364.085..5,561.269 rows=209,716 loops=1)

  • Merge Cond: (top_parent.loc_id = stock_quant.location_id)
15. 0.579 19.462 ↑ 23.8 1,560 1

Sort (cost=38,392.99..38,485.64 rows=37,062 width=8) (actual time=19.350..19.462 rows=1,560 loops=1)

  • Sort Key: top_parent.loc_id
  • Sort Method: quicksort Memory: 122kB
16. 1.426 18.883 ↑ 23.8 1,560 1

Hash Join (cost=1.11..35,580.42 rows=37,062 width=8) (actual time=0.101..18.883 rows=1,560 loops=1)

  • Hash Cond: (top_parent.top_parent_id = stock_warehouse.lot_stock_id)
17. 17.441 17.441 ↑ 331.6 4,470 1

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

18. 0.004 0.016 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.012 0.012 ↑ 1.0 5 1

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

20. 380.543 5,118.346 ↑ 1.0 5,307,047 1

Materialize (cost=1,015,552.57..1,042,093.86 rows=5,308,258 width=28) (actual time=4,263.639..5,118.346 rows=5,307,047 loops=1)

21. 1,964.490 4,737.803 ↑ 1.0 5,307,047 1

Sort (cost=1,015,552.57..1,028,823.22 rows=5,308,258 width=28) (actual time=4,263.636..4,737.803 rows=5,307,047 loops=1)

  • Sort Key: stock_quant.location_id
  • Sort Method: external merge Disk: 197432kB
22. 918.327 2,773.313 ↑ 1.0 5,308,258 1

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

  • Hash Cond: (product_product.product_tmpl_id = product_template.id)
23. 1,192.441 1,848.608 ↑ 1.0 5,308,258 1

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

  • Hash Cond: (stock_quant.product_id = product_product.id)
24. 649.609 649.609 ↑ 1.0 5,308,258 1

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

25. 2.338 6.558 ↑ 1.0 15,595 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 738kB
26. 4.220 4.220 ↑ 1.0 15,595 1

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

27. 2.109 6.378 ↑ 1.0 15,595 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 738kB
28. 4.269 4.269 ↑ 1.0 15,595 1

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

29. 18.194 16,942.826 ↑ 4.5 92,769 1

Hash (cost=4,320,631.99..4,320,631.99 rows=416,260 width=16) (actual time=16,942.826..16,942.826 rows=92,769 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 1571kB
30. 7.407 16,924.632 ↑ 4.5 92,769 1

Subquery Scan on max_stock_move (cost=4,037,367.90..4,320,631.99 rows=416,260 width=16) (actual time=13,613.000..16,924.632 rows=92,769 loops=1)

31. 32.498 16,917.225 ↑ 4.5 92,769 1

Finalize GroupAggregate (cost=4,037,367.90..4,316,469.39 rows=416,260 width=16) (actual time=13,612.999..16,917.225 rows=92,769 loops=1)

  • Group Key: m.location_id, m.product_id
32. 0.000 16,884.727 ↑ 3.9 211,772 1

Gather Merge (cost=4,037,367.90..4,306,062.89 rows=832,520 width=16) (actual time=13,612.995..16,884.727 rows=211,772 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 4,908.738 50,393.163 ↑ 5.9 70,591 3

Partial GroupAggregate (cost=4,036,367.88..4,208,969.40 rows=416,260 width=16) (actual time=13,595.877..16,797.721 rows=70,591 loops=3)

  • Group Key: m.location_id, m.product_id
34. 28,608.900 45,484.425 ↑ 1.3 13,475,113 3

Sort (cost=4,036,367.88..4,078,477.61 rows=16,843,892 width=16) (actual time=13,595.872..15,161.475 rows=13,475,113 loops=3)

  • Sort Key: m.location_id, m.product_id
  • Sort Method: external merge Disk: 344232kB
35. 16,875.525 16,875.525 ↑ 1.3 13,475,113 3

Parallel Seq Scan on stock_move m (cost=0.00..1,438,896.92 rows=16,843,892 width=16) (actual time=0.025..5,625.175 rows=13,475,113 loops=3)

36. 0.069 0.164 ↑ 1.0 129 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
37. 0.095 0.095 ↑ 1.0 129 1

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

38. 0.046 1.900 ↑ 1.0 466 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
39. 0.037 1.854 ↑ 1.0 466 1

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

40. 1.307 1.817 ↑ 1.0 466 1

HashAggregate (cost=221.69..226.35 rows=466 width=12) (actual time=1.764..1.817 rows=466 loops=1)

  • Group Key: s.location_id
41. 0.510 0.510 ↓ 1.0 6,647 1

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