explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EZ4

Settings
# exclusive inclusive rows x rows loops node
1. 72.169 6,720.569 ↑ 4.5 29,904 1

Merge Join (cost=892,644.71..917,198.78 rows=135,777 width=412) (actual time=6,622.113..6,720.569 rows=29,904 loops=1)

  • Buffers: shared hit=4274 read=75587, temp read=17891 written=17947
2.          

CTE _distinct_delivery_sites

3. 18.544 199.496 ↑ 1.2 11,613 1

Unique (cost=29,371.56..30,702.38 rows=13,782 width=50) (actual time=161.783..199.496 rows=11,613 loops=1)

  • Buffers: shared hit=2642, temp read=977 written=979
4. 93.239 180.952 ↑ 1.3 140,198 1

Sort (cost=29,371.56..29,815.17 rows=177,442 width=50) (actual time=161.781..180.952 rows=140,198 loops=1)

  • Sort Key: imp_cat_centre_profit_assortiment_region."CENTRE_PROFIT", imp_cat_centre_profit_assortiment_region."REGION_LIVRAISON"
  • Sort Method: external merge Disk: 3912kB
  • Buffers: shared hit=2642, temp read=977 written=979
5. 26.230 87.713 ↑ 1.3 140,198 1

Hash Join (cost=5,970.5..7,835.79 rows=177,442 width=50) (actual time=61.487..87.713 rows=140,198 loops=1)

  • Buffers: shared hit=2642, temp read=488 written=488
6. 0.168 0.676 ↑ 1.0 609 1

Hash Join (cost=34.64..633.34 rows=609 width=42) (actual time=0.18..0.676 rows=609 loops=1)

  • Buffers: shared hit=615
7. 0.341 0.341 ↑ 1.0 609 1

Seq Scan on Site_Parent_Tree Site_Parent_Tree (cost=0..597.09 rows=609 width=12) (actual time=0.003..0.341 rows=609 loops=1)

  • Buffers: shared hit=591
8. 0.076 0.167 ↑ 1.0 473 1

Hash (cost=28.73..28.73 rows=473 width=38) (actual time=0.167..0.167 rows=473 loops=1)

  • Buffers: shared hit=24
9. 0.091 0.091 ↑ 1.0 473 1

Seq Scan on Site Site (cost=0..28.73 rows=473 width=38) (actual time=0.005..0.091 rows=473 loops=1)

  • Buffers: shared hit=24
10. 36.572 60.807 ↑ 1.0 137,816 1

Hash (cost=3,405.16..3,405.16 rows=137,816 width=17) (actual time=60.807..60.807 rows=137,816 loops=1)

  • Buffers: shared hit=2027, temp written=479
11. 24.235 24.235 ↑ 1.0 137,816 1

Seq Scan on imp_cat_centre_profit_assortiment_region imp_cat_centre_profit_assortiment_region (cost=0..3,405.16 rows=137,816 width=17) (actual time=0.995..24.235 rows=137,816 loops=1)

  • Buffers: shared hit=2027
12.          

CTE _all_suppliers_by_delivery_sites

13. 19.446 441.388 ↓ 1.2 27,443 1

Unique (cost=7,786.15..7,957.58 rows=22,858 width=222) (actual time=393.703..441.388 rows=27,443 loops=1)

  • Buffers: shared hit=3435, temp read=1958 written=1964
14. 141.444 421.942 ↓ 6.1 140,576 1

Sort (cost=7,786.15..7,843.29 rows=22,858 width=222) (actual time=393.702..421.942 rows=140,576 loops=1)

  • Sort Key: imp_cat_fournisseur_region."CODE_FOURNISSEUR", _distinct_delivery_sites."deliveryLocation"
  • Sort Method: external merge Disk: 7848kB
  • Buffers: shared hit=3435, temp read=1958 written=1964
15. 30.736 280.498 ↓ 6.1 140,576 1

Merge Join (cost=3,331.34..3,709.18 rows=22,858 width=222) (actual time=238.161..280.498 rows=140,576 loops=1)

  • Buffers: shared hit=3435, temp read=977 written=979
16. 20.662 36.044 ↓ 4.0 27,740 1

Sort (cost=2,108.16..2,125.63 rows=6,991 width=42) (actual time=32.846..36.044 rows=27,740 loops=1)

  • Sort Key: imp_cat_fournisseur_region."REGION_LIVRAISON"
  • Sort Method: quicksort Memory: 3273kB
  • Buffers: shared hit=793
17. 11.004 15.382 ↓ 4.0 27,740 1

Hash Join (cost=39.48..1,661.73 rows=6,991 width=42) (actual time=0.516..15.382 rows=27,740 loops=1)

  • Buffers: shared hit=793
18. 4.127 4.127 ↑ 1.0 52,490 1

Seq Scan on imp_cat_fournisseur_region imp_cat_fournisseur_region (cost=0..1,289.9 rows=52,490 width=20) (actual time=0.258..4.127 rows=52,490 loops=1)

  • Buffers: shared hit=765
19. 0.118 0.251 ↑ 1.0 510 1

Hash (cost=33.1..33.1 rows=510 width=31) (actual time=0.251..0.251 rows=510 loops=1)

  • Buffers: shared hit=28
20. 0.133 0.133 ↑ 1.0 510 1

Seq Scan on Supplier Supplier (cost=0..33.1 rows=510 width=31) (actual time=0.005..0.133 rows=510 loops=1)

  • Buffers: shared hit=28
21. 12.514 213.718 ↓ 10.2 140,624 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=190) (actual time=205.304..213.718 rows=140,624 loops=1)

  • Sort Key: _distinct_delivery_sites."deliveryLocation"
  • Sort Method: quicksort Memory: 1108kB
  • Buffers: shared hit=2642, temp read=977 written=979
22. 201.204 201.204 ↑ 1.2 11,613 1

CTE Scan on _distinct_delivery_sites _distinct_delivery_sites (cost=0..275.64 rows=13,782 width=190) (actual time=161.786..201.204 rows=11,613 loops=1)

  • Buffers: shared hit=2642, temp read=977 written=979
23.          

CTE _all_companies

24. 301.406 6,161.624 ↓ 1.1 11,308 1

Unique (cost=822,813.87..847,348.76 rows=10,692 width=22) (actual time=5,244.982..6,161.624 rows=11,308 loops=1)

  • Buffers: shared hit=839 read=75587, temp read=15933 written=15983
25. 3,980.046 5,860.218 ↑ 1.3 2,533,234 1

Sort (cost=822,813.87..830,992.17 rows=3,271,319 width=22) (actual time=5,244.98..5,860.218 rows=2,533,234 loops=1)

  • Sort Key: imp_cat_tarif."CODE_SOCIETE", imp_cat_tarif."CODE_FOURNISSEUR"
  • Sort Method: external merge Disk: 66312kB
  • Buffers: shared hit=839 read=75587, temp read=15933 written=15983
26. 424.650 1,880.172 ↑ 1.3 2,533,234 1

Hash Join (cost=639.35..334,657.58 rows=3,271,319 width=22) (actual time=417.145..1,880.172 rows=2,533,234 loops=1)

  • Buffers: shared hit=839 read=75587
27. 591.030 1,454.994 ↑ 1.0 2,533,234 1

Hash Join (cost=34.64..288,635.8 rows=2,540,778 width=18) (actual time=416.609..1,454.994 rows=2,533,234 loops=1)

  • Buffers: shared hit=248 read=75587
28. 863.760 863.760 ↑ 1.0 2,533,234 1

Seq Scan on imp_cat_tarif imp_cat_tarif (cost=0..101,218.78 rows=2,540,778 width=14) (actual time=416.386..863.76 rows=2,533,234 loops=1)

  • Buffers: shared hit=224 read=75587
29. 0.103 0.204 ↑ 1.0 473 1

Hash (cost=28.73..28.73 rows=473 width=13) (actual time=0.204..0.204 rows=473 loops=1)

  • Buffers: shared hit=24
30. 0.101 0.101 ↑ 1.0 473 1

Seq Scan on Site Site_1 (cost=0..28.73 rows=473 width=13) (actual time=0.007..0.101 rows=473 loops=1)

  • Buffers: shared hit=24
31. 0.086 0.528 ↑ 1.0 609 1

Hash (cost=597.09..597.09 rows=609 width=12) (actual time=0.528..0.528 rows=609 loops=1)

  • Buffers: shared hit=591
32. 0.442 0.442 ↑ 1.0 609 1

Seq Scan on Site_Parent_Tree Site_Parent_Tree_1 (cost=0..597.09 rows=609 width=12) (actual time=0.004..0.442 rows=609 loops=1)

  • Buffers: shared hit=591
33. 8.034 453.054 ↓ 1.2 27,443 1

Sort (cost=5,706.63..5,763.77 rows=22,858 width=340) (actual time=451.661..453.054 rows=27,443 loops=1)

  • Sort Key: _all_suppliers_by_delivery_sites."supplier_extId"
  • Sort Method: quicksort Memory: 3970kB
  • Buffers: shared hit=3435, temp read=1958 written=1964
34. 445.020 445.020 ↓ 1.2 27,443 1

CTE Scan on _all_suppliers_by_delivery_sites _all_suppliers_by_delivery_sites (cost=0..457.16 rows=22,858 width=340) (actual time=393.705..445.02 rows=27,443 loops=1)

  • Buffers: shared hit=3435, temp read=1958 written=1964
35. 30.382 6,195.346 ↓ 56.6 604,648 1

Sort (cost=929.36..956.09 rows=10,692 width=72) (actual time=6,170.439..6,195.346 rows=604,648 loops=1)

  • Sort Key: _all_companies."supplier_extId"
  • Sort Method: quicksort Memory: 1011kB
  • Buffers: shared hit=839 read=75587, temp read=15933 written=15983
36. 6,164.964 6,164.964 ↓ 1.1 11,308 1

CTE Scan on _all_companies _all_companies (cost=0..213.84 rows=10,692 width=72) (actual time=5,244.984..6,164.964 rows=11,308 loops=1)

  • Buffers: shared hit=839 read=75587, temp read=15933 written=15983