explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m7J8

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

Merge Join (cost=728,466.74..1,526,782.35 rows=4,420,661 width=300) (actual time=4,465.576..5,062.652 rows=165,370 loops=1)

  • Buffers: shared hit=4498 read=75363, temp read=15020 written=16276
2.          

CTE _distinct_delivery_sites

3. 18.440 116.291 ↑ 1.2 11,613 1

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

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

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=82.249..97.851 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. 16.561 16.561 ↑ 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.547..16.561 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 2.328 125.156 ↑ 1.7 11,809 1

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

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

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

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

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

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

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

  • Buffers: shared hit=591
11. 0.088 0.201 ↑ 1.0 473 1

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

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

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

  • Buffers: shared hit=24
13. 3.065 121.624 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=120.956..121.624 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. 118.559 118.559 ↑ 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=82.256..118.559 rows=11,613 loops=1)

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

CTE _distinct_suppliers_by_locations

16. 10.838 94.024 ↓ 10.0 52,490 1

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

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

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=75.744..83.186 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. 9.767 9.767 ↑ 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.494..9.767 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 28.617 312.962 ↑ 3.6 143,432 1

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

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

Sort (cost=548.31..561.46 rows=5,259 width=86) (actual time=137.046..139.18 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. 11.307 118.981 ↓ 5.3 27,740 1

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

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

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

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

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

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

  • Buffers: shared hit=28
26. 9.049 145.165 ↓ 7.3 143,480 1

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

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

Sort (cost=3,627.52..3,676.89 rows=19,749 width=190) (actual time=134.897..136.116 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. 128.855 128.855 ↑ 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=122.121..128.855 rows=11,809 loops=1)

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

CTE _distinct_companies

30. 277.685 4,000.010 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=448 read=75363, temp read=13484 written=13529
31. 3,221.378 3,722.325 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,174.834..3,722.325 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=448 read=75363, temp read=13484 written=13529
32. 500.947 500.947 ↑ 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=113.84..500.947 rows=2,533,234 loops=1)

  • Buffers: shared hit=448 read=75363
33.          

CTE _distinct_companies_with_borders

34. 2.079 4,011.301 ↑ 1.4 11,308 1

Merge Join (cost=1,590.87..1,823.73 rows=15,322 width=72) (actual time=4,008.698..4,011.301 rows=11,308 loops=1)

  • Buffers: shared hit=1063 read=75363, temp read=13484 written=13529
35. 0.291 1.100 ↑ 9.7 63 1

Sort (cost=661.51..663.03 rows=609 width=17) (actual time=1.081..1.1 rows=63 loops=1)

  • Sort Key: "Site_1"."extId"
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=615
36. 0.171 0.809 ↑ 1.0 609 1

Hash Join (cost=34.64..633.34 rows=609 width=17) (actual time=0.219..0.809 rows=609 loops=1)

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

  • Buffers: shared hit=591
38. 0.075 0.207 ↑ 1.0 473 1

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

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

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

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

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

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

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

  • Buffers: shared hit=448 read=75363, temp read=13484 written=13529
42. 100.097 481.225 ↑ 3.6 143,432 1

Sort (cost=208,794.48..210,092.8 rows=519,331 width=308) (actual time=446.22..481.225 rows=143,432 loops=1)

  • Sort Key: _all_suppliers_by_delivery_sites."supplier_extId"
  • Sort Method: external merge Disk: 7040kB
  • Buffers: shared hit=3435, temp read=1536 written=2747
43. 381.128 381.128 ↑ 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=308) (actual time=271.973..381.128 rows=143,432 loops=1)

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

Sort (cost=1,371.57..1,409.88 rows=15,322 width=40) (actual time=4,019.345..4,165.238 rows=3,156,404 loops=1)

  • Sort Key: _distinct_companies_with_borders."supplier_extId"
  • Sort Method: quicksort Memory: 1011kB
  • Buffers: shared hit=1063 read=75363, temp read=13484 written=13529
45. 4,015.073 4,015.073 ↑ 1.4 11,308 1

CTE Scan on _distinct_companies_with_borders _distinct_companies_with_borders (cost=0..306.44 rows=15,322 width=40) (actual time=4,008.713..4,015.073 rows=11,308 loops=1)

  • Buffers: shared hit=1063 read=75363, temp read=13484 written=13529