explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 46.448 92,876.122 ↑ 1,506.1 6,420 1

GroupAggregate (cost=95,866,077,244.52..95,895,454,036.05 rows=9,668,900 width=102) (actual time=92,800.452..92,876.122 rows=6,420 loops=1)

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

CTE top_parent

3. 1.186 6.225 ↑ 331.6 4,470 1

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

4. 0.443 0.443 ↑ 1.0 1,811 1

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

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

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

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

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

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

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

Sort (cost=1,642.98..1,688.26 rows=18,110 width=8) (actual time=0.241..0.308 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.          

CTE matching_stock_inventory

11. 0.759 6.186 ↑ 1.0 458 1

GroupAggregate (cost=202,478.45..629,076.70 rows=466 width=12) (actual time=2.632..6.186 rows=458 loops=1)

  • Group Key: stock_inventory.location_id
12. 1.717 5.427 ↑ 2,263.2 9,342 1

Merge Join (cost=202,478.45..523,358.49 rows=21,142,709 width=12) (actual time=2.623..5.427 rows=9,342 loops=1)

  • Merge Cond: (stock_inventory.location_id = top_parent_1.loc_id)
13. 1.013 1.929 ↓ 1.0 6,647 1

Sort (cost=610.42..627.04 rows=6,646 width=12) (actual time=1.640..1.929 rows=6,647 loops=1)

  • Sort Key: stock_inventory.location_id
  • Sort Method: quicksort Memory: 504kB
14. 0.916 0.916 ↓ 1.0 6,647 1

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

15. 0.628 1.781 ↑ 117.9 12,573 1

Materialize (cost=201,868.03..209,280.38 rows=1,482,471 width=4) (actual time=0.979..1.781 rows=12,573 loops=1)

16. 0.805 1.153 ↑ 331.9 4,467 1

Sort (cost=201,868.03..205,574.21 rows=1,482,471 width=4) (actual time=0.978..1.153 rows=4,467 loops=1)

  • Sort Key: top_parent_1.loc_id
  • Sort Method: quicksort Memory: 402kB
17. 0.348 0.348 ↑ 331.6 4,470 1

CTE Scan on top_parent top_parent_1 (cost=0.00..29,649.42 rows=1,482,471 width=4) (actual time=0.001..0.348 rows=4,470 loops=1)

18.          

CTE matching_stock_move

19. 5,185.550 85,236.414 ↑ 5.3 77,987 1

GroupAggregate (cost=92,348,803,246.34..95,345,277,119.70 rows=416,260 width=16) (actual time=75,231.249..85,236.414 rows=77,987 loops=1)

  • Group Key: stock_move.location_id, stock_move.product_id
20. 28,836.458 80,050.864 ↑ 6,905.0 43,395,653 1

Sort (cost=92,348,803,246.34..93,097,920,674.03 rows=299,646,971,076 width=16) (actual time=75,231.241..80,050.864 rows=43,395,653 loops=1)

  • Sort Key: stock_move.location_id, stock_move.product_id
  • Sort Method: external merge Disk: 1104048kB
21. 5,258.367 51,214.406 ↑ 6,905.0 43,395,653 1

Merge Join (cost=9,056,664.81..4,503,869,706.65 rows=299,646,971,076 width=16) (actual time=35,856.078..51,214.406 rows=43,395,653 loops=1)

  • Merge Cond: (top_parent_2.loc_id = stock_move.location_id)
22. 0.937 1.292 ↑ 331.7 4,469 1

Sort (cost=201,868.03..205,574.21 rows=1,482,471 width=4) (actual time=0.960..1.292 rows=4,469 loops=1)

  • Sort Key: top_parent_2.loc_id
  • Sort Method: quicksort Memory: 402kB
23. 0.355 0.355 ↑ 331.6 4,470 1

CTE Scan on top_parent top_parent_2 (cost=0.00..29,649.42 rows=1,482,471 width=4) (actual time=0.001..0.355 rows=4,470 loops=1)

24. 5,115.425 45,954.747 ↓ 1.1 45,496,079 1

Materialize (cost=8,854,796.78..9,056,923.48 rows=40,425,340 width=16) (actual time=35,380.461..45,954.747 rows=45,496,079 loops=1)

25. 29,248.808 40,839.322 ↑ 1.0 40,425,339 1

Sort (cost=8,854,796.78..8,955,860.13 rows=40,425,340 width=16) (actual time=35,380.458..40,839.322 rows=40,425,339 loops=1)

  • Sort Key: stock_move.location_id
  • Sort Method: external merge Disk: 1028520kB
26. 11,590.514 11,590.514 ↑ 1.0 40,425,339 1

Seq Scan on stock_move (cost=0.00..1,674,711.40 rows=40,425,340 width=16) (actual time=0.603..11,590.514 rows=40,425,339 loops=1)

27. 130.883 92,829.674 ↑ 6,174.5 209,716 1

Sort (cost=520,100,285.23..523,337,515.12 rows=1,294,891,957 width=70) (actual time=92,800.439..92,829.674 rows=209,716 loops=1)

  • Sort Key: stock_warehouse.id, product_product.id, product_category.name
  • Sort Method: external merge Disk: 15944kB
28. 30.959 92,698.791 ↑ 6,174.5 209,716 1

Hash Left Join (cost=21,538,911.19..111,673,968.47 rows=1,294,891,957 width=70) (actual time=91,347.373..92,698.791 rows=209,716 loops=1)

  • Hash Cond: (top_parent.loc_id = matching_stock_inventory.location_id)
29. 33.811 92,661.489 ↑ 2,650.0 209,716 1

Hash Join (cost=21,538,896.05..64,685,491.97 rows=555,747,621 width=66) (actual time=91,341.016..92,661.489 rows=209,716 loops=1)

  • Hash Cond: (product_template.categ_id = product_category.id)
30. 1,731.393 92,627.623 ↑ 2,650.0 209,716 1

Hash Right Join (cost=21,538,887.15..63,178,717.72 rows=555,747,621 width=40) (actual time=91,340.956..92,627.623 rows=209,716 loops=1)

  • Hash Cond: ((matching_stock_move.location_id = top_parent.loc_id) AND (matching_stock_move.product_id = product_product.id))
31. 85,260.715 85,260.715 ↑ 5.3 77,987 1

CTE Scan on matching_stock_move (cost=0.00..8,325.20 rows=416,260 width=16) (actual time=75,231.252..85,260.715 rows=77,987 loops=1)

32. 104.617 5,635.515 ↑ 2,650.0 209,716 1

Hash (cost=9,403,615.83..9,403,615.83 rows=555,747,621 width=32) (actual time=5,635.515..5,635.515 rows=209,716 loops=1)

  • Buckets: 65536 Batches: 16384 Memory Usage: 1408kB
33. 426.005 5,530.898 ↑ 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,322.170..5,530.898 rows=209,716 loops=1)

  • Merge Cond: (top_parent.loc_id = stock_quant.location_id)
34. 0.384 7.841 ↑ 23.8 1,560 1

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

  • Sort Key: top_parent.loc_id
  • Sort Method: quicksort Memory: 122kB
35. 0.508 7.457 ↑ 23.8 1,560 1

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

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

37. 0.001 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
38. 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)

39. 370.574 5,097.052 ↑ 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,249.281..5,097.052 rows=5,307,047 loops=1)

40. 1,974.233 4,726.478 ↑ 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,249.278..4,726.478 rows=5,307,047 loops=1)

  • Sort Key: stock_quant.location_id
  • Sort Method: external merge Disk: 197432kB
41. 913.640 2,752.245 ↑ 1.0 5,308,258 1

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

  • Hash Cond: (product_product.product_tmpl_id = product_template.id)
42. 1,205.703 1,832.822 ↑ 1.0 5,308,258 1

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

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

44. 1.623 4.729 ↑ 1.0 15,595 1

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

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

46. 2.073 5.783 ↑ 1.0 15,595 1

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

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

48. 0.018 0.055 ↑ 1.0 129 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
49. 0.037 0.037 ↑ 1.0 129 1

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

50. 0.071 6.343 ↑ 1.0 458 1

Hash (cost=9.32..9.32 rows=466 width=12) (actual time=6.343..6.343 rows=458 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
51. 6.272 6.272 ↑ 1.0 458 1

CTE Scan on matching_stock_inventory (cost=0.00..9.32 rows=466 width=12) (actual time=2.633..6.272 rows=458 loops=1)