explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lv2v

Settings
# exclusive inclusive rows x rows loops node
1. 520.949 5,035.250 ↑ 26.7 165,370 1

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

  • Buffers: shared hit=4402 read=75459, temp read=15154 written=16410
2.          

CTE _distinct_delivery_sites

3. 18.032 111.423 ↑ 1.2 11,613 1

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

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

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=77.871..93.391 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. 15.854 15.854 ↑ 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.66..15.854 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 2.310 120.383 ↑ 1.7 11,809 1

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

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

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

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

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

  • Buffers: shared hit=615
10. 0.417 0.417 ↑ 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.417 rows=609 loops=1)

  • Buffers: shared hit=591
11. 0.076 0.181 ↑ 1.0 473 1

Hash (cost=28.73..28.73 rows=473 width=38) (actual time=0.181..0.181 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.01..0.105 rows=473 loops=1)

  • Buffers: shared hit=24
13. 3.044 116.723 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=115.988..116.723 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. 113.679 113.679 ↑ 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=77.874..113.679 rows=11,613 loops=1)

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

CTE _distinct_suppliers_by_locations

16. 9.369 93.413 ↓ 10.0 52,490 1

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

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

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=77.11..84.044 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. 12.122 12.122 ↑ 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.888..12.122 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 28.534 299.572 ↑ 3.6 143,432 1

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

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

Sort (cost=548.31..561.46 rows=5,259 width=86) (actual time=131.948..133.609 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. 9.818 116.032 ↓ 5.3 27,740 1

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

  • Buffers: shared hit=793, temp read=197 written=393
23. 106.013 106.013 ↓ 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=77.114..106.013 rows=52,490 loops=1)

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

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

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

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

  • Buffers: shared hit=28
26. 8.356 137.429 ↓ 7.3 143,480 1

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

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

Sort (cost=3,627.52..3,676.89 rows=19,749 width=190) (actual time=128.105..129.073 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. 124.119 124.119 ↑ 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=117.194..124.119 rows=11,809 loops=1)

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

CTE _distinct_companies

30. 216.396 3,951.949 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=352 read=75459, temp read=13484 written=13529
31. 3,183.589 3,735.553 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,310.724..3,735.553 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=352 read=75459, temp read=13484 written=13529
32. 551.964 551.964 ↑ 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=127.518..551.964 rows=2,533,234 loops=1)

  • Buffers: shared hit=352 read=75459
33.          

CTE _distinct_companies_with_borders

34. 2.065 3,959.574 ↑ 1.4 11,308 1

Merge Join (cost=1,590.87..1,823.73 rows=15,322 width=273) (actual time=3,957.144..3,959.574 rows=11,308 loops=1)

  • Buffers: shared hit=967 read=75459, temp read=13484 written=13529
35. 0.483 1.488 ↑ 9.7 63 1

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

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

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

  • Buffers: shared hit=615
37. 0.398 0.398 ↑ 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.398 rows=609 loops=1)

  • Buffers: shared hit=591
38. 0.170 0.332 ↑ 1.0 473 1

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

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

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

  • Buffers: shared hit=24
40. 1.812 3,956.021 ↓ 1.1 11,308 1

Sort (cost=929.36..956.09 rows=10,692 width=64) (actual time=3,955.645..3,956.021 rows=11,308 loops=1)

  • Sort Key: _distinct_companies."company_extId"
  • Sort Method: quicksort Memory: 915kB
  • Buffers: shared hit=352 read=75459, temp read=13484 written=13529
41. 3,954.209 3,954.209 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=352 read=75459, temp read=13484 written=13529
42. 80.488 439.658 ↑ 3.6 143,432 1

Sort (cost=222,997.48..224,295.8 rows=519,331 width=340) (actual time=412.845..439.658 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. 359.170 359.170 ↑ 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=260.067..359.17 rows=143,432 loops=1)

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

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

  • Buffers: shared hit=967 read=75459, temp read=13484 written=13529
45. 3.730 3,968.022 ↑ 1.4 11,308 1

Sort (cost=9,596.57..9,634.88 rows=15,322 width=1,228) (actual time=3,967.136..3,968.022 rows=11,308 loops=1)

  • Sort Key: _distinct_companies_with_borders."supplier_extId"
  • Sort Method: quicksort Memory: 1846kB
  • Buffers: shared hit=967 read=75459, temp read=13484 written=13529
46. 3,964.292 3,964.292 ↑ 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=3,957.148..3,964.292 rows=11,308 loops=1)

  • Buffers: shared hit=967 read=75459, temp read=13484 written=13529