explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0el5 : Optimization for: Optimization for: plan #f9dx; plan #pEXz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 45.552 33,055.644 ↑ 1,506.1 6,420 1

GroupAggregate (cost=435,744,190.61..465,120,982.14 rows=9,668,900 width=102) (actual time=32,980.827..33,055.644 rows=6,420 loops=1)

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

CTE top_parent

3. 1.263 6.495 ↑ 331.6 4,470 1

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

4. 0.444 0.444 ↑ 1.0 1,811 1

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

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

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

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

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

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

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

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

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

10. 132.147 33,010.092 ↑ 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=32,980.810..33,010.092 rows=209,716 loops=1)

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

  • Hash Cond: (top_parent.loc_id = stock_inventory.location_id)
12. 34.107 32,845.520 ↑ 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=32,459.393..32,845.520 rows=209,716 loops=1)

  • Hash Cond: (product_template.categ_id = product_category.id)
13. 82.391 32,811.364 ↑ 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=32,459.341..32,811.364 rows=209,716 loops=1)

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

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

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

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

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

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

18. 0.002 0.006 ↑ 1.0 5 1

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

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

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

20. 391.176 5,034.973 ↑ 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,109.273..5,034.973 rows=5,307,047 loops=1)

21. 2,027.804 4,643.797 ↑ 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,109.271..4,643.797 rows=5,307,047 loops=1)

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

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

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

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

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

25. 1.774 5.269 ↑ 1.0 15,595 1

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

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

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

27. 1.774 5.960 ↑ 1.0 15,595 1

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

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

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

29. 18.652 27,248.594 ↑ 4.5 92,769 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 1571kB
30. 7.857 27,229.942 ↑ 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=23,878.777..27,229.942 rows=92,769 loops=1)

31. 33.162 27,222.085 ↑ 4.5 92,769 1

Finalize GroupAggregate (cost=4,037,367.90..4,316,469.39 rows=416,260 width=16) (actual time=23,878.776..27,222.085 rows=92,769 loops=1)

  • Group Key: m.location_id, m.product_id
32. 0.000 27,188.923 ↑ 3.9 211,912 1

Gather Merge (cost=4,037,367.90..4,306,062.89 rows=832,520 width=16) (actual time=23,878.773..27,188.923 rows=211,912 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 4,946.136 81,271.770 ↑ 5.9 70,637 3

Partial GroupAggregate (cost=4,036,367.88..4,208,969.40 rows=416,260 width=16) (actual time=23,855.562..27,090.590 rows=70,637 loops=3)

  • Group Key: m.location_id, m.product_id
34. 44,920.227 76,325.634 ↑ 1.3 13,475,113 3

Sort (cost=4,036,367.88..4,078,477.61 rows=16,843,892 width=16) (actual time=23,855.553..25,441.878 rows=13,475,113 loops=3)

  • Sort Key: m.location_id, m.product_id
  • Sort Method: external merge Disk: 343720kB
35. 31,405.407 31,405.407 ↑ 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.733..10,468.469 rows=13,475,113 loops=3)

36. 0.019 0.049 ↑ 1.0 129 1

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

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

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

38. 0.045 1.931 ↑ 1.0 466 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
39. 0.036 1.886 ↑ 1.0 466 1

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

40. 1.280 1.850 ↑ 1.0 466 1

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

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

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