explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BLPR : Optimization for: Optimization for: plan #f9dx; plan #pEXz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 45.789 23,284.825 ↑ 1,506.1 6,420 1

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

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

CTE top_parent

3. 1.251 6.460 ↑ 331.6 4,470 1

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

4. 0.457 0.457 ↑ 1.0 1,811 1

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

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

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

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

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

  • Sort Key: sl_1.location_id
  • Sort Method: quicksort Memory: 133kB
7. 1.388 1.388 ↑ 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.011..0.347 rows=1,811 loops=4)

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

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

  • Sort Key: tp.loc_id
  • Sort Method: quicksort Memory: 27kB
9. 0.324 0.324 ↑ 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.081 rows=1,118 loops=4)

10. 130.602 23,239.036 ↑ 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=23,209.866..23,239.036 rows=209,716 loops=1)

  • Sort Key: stock_warehouse.id, product_product.id, product_category.name
  • Sort Method: external merge Disk: 15944kB
11. 28.353 23,108.434 ↑ 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,777.312..23,108.434 rows=209,716 loops=1)

  • Hash Cond: (top_parent.loc_id = stock_inventory.location_id)
12. 32.299 23,078.238 ↑ 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,775.461..23,078.238 rows=209,716 loops=1)

  • Hash Cond: (product_template.categ_id = product_category.id)
13. 75.519 23,045.878 ↑ 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,775.392..23,045.878 rows=209,716 loops=1)

  • Hash Cond: ((top_parent.loc_id = stock_move.location_id) AND (product_product.id = stock_move.product_id))
14. 433.674 5,646.002 ↑ 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,450.941..5,646.002 rows=209,716 loops=1)

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

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

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

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

  • Hash Cond: (top_parent.top_parent_id = stock_warehouse.lot_stock_id)
17. 7.206 7.206 ↑ 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.027..7.206 rows=4,470 loops=1)

18. 0.002 0.007 ↑ 1.0 5 1

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

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

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

20. 370.359 5,204.235 ↑ 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,360.530..5,204.235 rows=5,307,047 loops=1)

21. 1,999.498 4,833.876 ↑ 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,360.528..4,833.876 rows=5,307,047 loops=1)

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

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

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

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

  • Hash Cond: (stock_quant.product_id = product_product.id)
24. 672.412 672.412 ↑ 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=1.915..672.412 rows=5,308,258 loops=1)

25. 1.794 5.435 ↑ 1.0 15,595 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 738kB
26. 3.641 3.641 ↑ 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..3.641 rows=15,595 loops=1)

27. 1.726 5.539 ↑ 1.0 15,595 1

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

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

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

29. 73.048 17,324.357 ↑ 4.5 92,769 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 1571kB
30. 7.358 17,251.309 ↑ 4.5 92,769 1

Subquery Scan on stock_move (cost=4,037,367.90..4,320,631.99 rows=416,260 width=16) (actual time=13,911.385..17,251.309 rows=92,769 loops=1)

31. 0.000 17,243.951 ↑ 4.5 92,769 1

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

  • Group Key: m.location_id, m.product_id
32. 0.000 18,488.849 ↑ 3.9 211,690 1

Gather Merge (cost=4,037,367.90..4,306,062.89 rows=832,520 width=16) (actual time=13,911.381..18,488.849 rows=211,690 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 4,920.444 51,270.783 ↑ 5.9 70,563 3

Partial GroupAggregate (cost=4,036,367.88..4,208,969.40 rows=416,260 width=16) (actual time=13,881.498..17,090.261 rows=70,563 loops=3)

  • Group Key: m.location_id, m.product_id
34. 27,742.170 46,350.339 ↑ 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,881.493..15,450.113 rows=13,475,113 loops=3)

  • Sort Key: m.location_id, m.product_id
  • Sort Method: external merge Disk: 344320kB
35. 18,608.169 18,608.169 ↑ 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.219..6,202.723 rows=13,475,113 loops=3)

36. 0.020 0.061 ↑ 1.0 129 1

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

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

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

38. 0.044 1.843 ↑ 1.0 466 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
39. 0.034 1.799 ↑ 1.0 466 1

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

40. 1.270 1.765 ↑ 1.0 466 1

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

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

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