explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X6uK

Settings
# exclusive inclusive rows x rows loops node
1. 652.834 6,021.783 ↑ 26.7 165,370 1

Merge Join (cost=750,894.74..1,549,248.66 rows=4,420,661 width=1,568) (actual time=5,195.277..6,021.783 rows=165,370 loops=1)

  • Buffers: shared hit=4338 read=75523, temp read=15154 written=16410
2.          

CTE _distinct_delivery_sites

3. 19.269 120.268 ↑ 1.2 11,613 1

Unique (cost=17,997.4..19,031.02 rows=13,782 width=17) (actual time=85.165..120.268 rows=11,613 loops=1)

  • Buffers: shared hit=2027, temp read=459 written=461
4. 82.760 100.999 ↑ 1.0 137,816 1

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=85.164..100.999 rows=137,816 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: 3672kB
  • Buffers: shared hit=2027, temp read=459 written=461
5. 18.239 18.239 ↑ 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.556..18.239 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 3.244 130.900 ↑ 1.7 11,809 1

Merge Join (cost=1,884.7..2,183.98 rows=19,749 width=97) (actual time=126.422..130.9 rows=11,809 loops=1)

  • Buffers: shared hit=2642, temp read=459 written=461
8. 0.573 1.312 ↑ 1.0 609 1

Sort (cost=661.51..663.03 rows=609 width=42) (actual time=1.045..1.312 rows=609 loops=1)

  • Sort Key: "Site"."extId"
  • Sort Method: quicksort Memory: 85kB
  • Buffers: shared hit=615
9. 0.169 0.739 ↑ 1.0 609 1

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

  • Buffers: shared hit=615
10. 0.381 0.381 ↑ 1.0 609 1

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

  • Buffers: shared hit=591
11. 0.084 0.189 ↑ 1.0 473 1

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

  • Buffers: shared hit=24
12. 0.105 0.105 ↑ 1.0 473 1

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

  • Buffers: shared hit=24
13. 3.811 126.344 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=125.356..126.344 rows=11,809 loops=1)

  • Sort Key: _distinct_delivery_sites."site_extId"
  • Sort Method: quicksort Memory: 1094kB
  • Buffers: shared hit=2027, temp read=459 written=461
14. 122.533 122.533 ↑ 1.2 11,613 1

CTE Scan on _distinct_delivery_sites _distinct_delivery_sites (cost=0..275.64 rows=13,782 width=64) (actual time=85.166..122.533 rows=11,613 loops=1)

  • Buffers: shared hit=2027, temp read=459 written=461
15.          

CTE _distinct_suppliers_by_locations

16. 10.374 86.307 ↓ 10.0 52,490 1

Unique (cost=5,405.05..5,798.73 rows=5,249 width=20) (actual time=68.598..86.307 rows=52,490 loops=1)

  • Buffers: shared hit=765, temp read=197 written=198
17. 67.493 75.933 ↑ 1.0 52,490 1

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=68.596..75.933 rows=52,490 loops=1)

  • Sort Key: imp_cat_fournisseur_region."CODE_FOURNISSEUR", imp_cat_fournisseur_region."REGION_LIVRAISON"
  • Sort Method: external merge Disk: 1576kB
  • Buffers: shared hit=765, temp read=197 written=198
18. 8.440 8.440 ↑ 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.24..8.44 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 33.758 315.911 ↑ 3.6 143,432 1

Merge Join (cost=4,175.83..12,041 rows=519,331 width=244) (actual time=267.933..315.911 rows=143,432 loops=1)

  • Buffers: shared hit=3435, temp read=656 written=854
21. 20.012 128.104 ↓ 5.3 27,740 1

Sort (cost=548.31..561.46 rows=5,259 width=86) (actual time=126.147..128.104 rows=27,740 loops=1)

  • Sort Key: _distinct_suppliers_by_locations."deliveryLocation"
  • Sort Method: quicksort Memory: 3273kB
  • Buffers: shared hit=793, temp read=197 written=393
22. 10.783 108.092 ↓ 5.3 27,740 1

Hash Join (cost=39.48..223.29 rows=5,259 width=86) (actual time=69.021..108.092 rows=27,740 loops=1)

  • Buffers: shared hit=793, temp read=197 written=393
23. 96.901 96.901 ↓ 10.0 52,490 1

CTE Scan on _distinct_suppliers_by_locations _distinct_suppliers_by_locations (cost=0..104.98 rows=5,249 width=64) (actual time=68.599..96.901 rows=52,490 loops=1)

  • Buffers: shared hit=765, temp read=197 written=393
24. 0.178 0.408 ↑ 1.0 510 1

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

  • Buffers: shared hit=28
25. 0.230 0.230 ↑ 1.0 510 1

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

  • Buffers: shared hit=28
26. 10.901 154.049 ↓ 7.3 143,480 1

Materialize (cost=3,627.52..3,726.27 rows=19,749 width=190) (actual time=141.777..154.049 rows=143,480 loops=1)

  • Buffers: shared hit=2642, temp read=459 written=461
27. 7.052 143.148 ↑ 1.7 11,809 1

Sort (cost=3,627.52..3,676.89 rows=19,749 width=190) (actual time=141.772..143.148 rows=11,809 loops=1)

  • Sort Key: _distinct_delivery_sites_with_labels."deliveryLocation"
  • Sort Method: quicksort Memory: 1133kB
  • Buffers: shared hit=2642, temp read=459 written=461
28. 136.096 136.096 ↑ 1.7 11,809 1

CTE Scan on _distinct_delivery_sites_with_labels _distinct_delivery_sites_with_labels (cost=0..394.98 rows=19,749 width=190) (actual time=126.425..136.096 rows=11,809 loops=1)

  • Buffers: shared hit=2642, temp read=459 written=461
29.          

CTE _distinct_companies

30. 284.255 4,725.648 ↓ 1.1 11,308 1

Unique (cost=458,366.4..477,422.24 rows=10,692 width=14) (actual time=3,881.753..4,725.648 rows=11,308 loops=1)

  • Buffers: shared hit=288 read=75523, temp read=13484 written=13529
31. 3,561.929 4,441.393 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,881.752..4,441.393 rows=2,533,234 loops=1)

  • Sort Key: imp_cat_tarif."CODE_SOCIETE", imp_cat_tarif."CODE_FOURNISSEUR"
  • Sort Method: external merge Disk: 59496kB
  • Buffers: shared hit=288 read=75523, temp read=13484 written=13529
32. 879.464 879.464 ↑ 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=376.615..879.464 rows=2,533,234 loops=1)

  • Buffers: shared hit=288 read=75523
33.          

CTE _distinct_companies_with_borders

34. 2.654 4,736.267 ↑ 1.4 11,308 1

Merge Join (cost=1,590.87..1,823.73 rows=15,322 width=273) (actual time=4,733.131..4,736.267 rows=11,308 loops=1)

  • Buffers: shared hit=903 read=75523, temp read=13484 written=13529
35. 0.492 1.500 ↑ 9.7 63 1

Sort (cost=661.51..663.03 rows=609 width=201) (actual time=1.495..1.5 rows=63 loops=1)

  • Sort Key: "Site_1"."extId"
  • Sort Method: quicksort Memory: 188kB
  • Buffers: shared hit=615
36. 0.279 1.008 ↑ 1.0 609 1

Hash Join (cost=34.64..633.34 rows=609 width=201) (actual time=0.353..1.008 rows=609 loops=1)

  • Buffers: shared hit=615
37. 0.387 0.387 ↑ 1.0 609 1

Seq Scan on Site_Parent_Tree Site_Parent_Tree_1 (cost=0..597.09 rows=609 width=20) (actual time=0.003..0.387 rows=609 loops=1)

  • Buffers: shared hit=591
38. 0.173 0.342 ↑ 1.0 473 1

Hash (cost=28.73..28.73 rows=473 width=181) (actual time=0.342..0.342 rows=473 loops=1)

  • Buffers: shared hit=24
39. 0.169 0.169 ↑ 1.0 473 1

Seq Scan on Site Site_1 (cost=0..28.73 rows=473 width=181) (actual time=0.011..0.169 rows=473 loops=1)

  • Buffers: shared hit=24
40. 3.171 4,732.113 ↓ 1.1 11,308 1

Sort (cost=929.36..956.09 rows=10,692 width=64) (actual time=4,731.62..4,732.113 rows=11,308 loops=1)

  • Sort Key: _distinct_companies."company_extId"
  • Sort Method: quicksort Memory: 915kB
  • Buffers: shared hit=288 read=75523, temp read=13484 written=13529
41. 4,728.942 4,728.942 ↓ 1.1 11,308 1

CTE Scan on _distinct_companies _distinct_companies (cost=0..213.84 rows=10,692 width=64) (actual time=3,881.755..4,728.942 rows=11,308 loops=1)

  • Buffers: shared hit=288 read=75523, temp read=13484 written=13529
42. 96.514 489.578 ↑ 3.6 143,432 1

Sort (cost=222,997.48..224,295.8 rows=519,331 width=340) (actual time=449.587..489.578 rows=143,432 loops=1)

  • Sort Key: _all_suppliers_by_delivery_sites."supplier_extId"
  • Sort Method: external merge Disk: 8112kB
  • Buffers: shared hit=3435, temp read=1670 written=2881
43. 393.064 393.064 ↑ 3.6 143,432 1

CTE Scan on _all_suppliers_by_delivery_sites _all_suppliers_by_delivery_sites (cost=0..10,386.62 rows=519,331 width=340) (actual time=267.936..393.064 rows=143,432 loops=1)

  • Buffers: shared hit=3435, temp read=656 written=1863
44. 132.594 4,879.371 ↓ 206.0 3,156,404 1

Materialize (cost=9,596.57..9,673.18 rows=15,322 width=1,228) (actual time=4,745.674..4,879.371 rows=3,156,404 loops=1)

  • Buffers: shared hit=903 read=75523, temp read=13484 written=13529
45. 4.822 4,746.777 ↑ 1.4 11,308 1

Sort (cost=9,596.57..9,634.88 rows=15,322 width=1,228) (actual time=4,745.671..4,746.777 rows=11,308 loops=1)

  • Sort Key: _distinct_companies_with_borders."supplier_extId"
  • Sort Method: quicksort Memory: 1846kB
  • Buffers: shared hit=903 read=75523, temp read=13484 written=13529
46. 4,741.955 4,741.955 ↑ 1.4 11,308 1

CTE Scan on _distinct_companies_with_borders _distinct_companies_with_borders (cost=0..306.44 rows=15,322 width=1,228) (actual time=4,733.135..4,741.955 rows=11,308 loops=1)

  • Buffers: shared hit=903 read=75523, temp read=13484 written=13529