explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MRtS

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

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

  • Buffers: shared hit=4373 read=75491, temp read=15020 written=16276
2.          

CTE _distinct_delivery_sites

3. 19.148 129.909 ↑ 1.2 11,613 1

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

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

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=94.475..110.761 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. 20.541 20.541 ↑ 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.855..20.541 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 1.865 138.221 ↑ 1.7 11,809 1

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

  • Buffers: shared hit=2645, temp read=459 written=461
8. 0.325 1.210 ↑ 1.0 609 1

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

  • Sort Key: "Site"."extId"
  • Sort Method: quicksort Memory: 85kB
  • Buffers: shared hit=618
9. 0.175 0.885 ↑ 1.0 609 1

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

  • Buffers: shared hit=618
10. 0.523 0.523 ↑ 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.523 rows=609 loops=1)

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

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

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

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

  • Buffers: shared hit=24
13. 3.052 135.146 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=134.621..135.146 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. 132.094 132.094 ↑ 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=94.477..132.094 rows=11,613 loops=1)

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

CTE _distinct_suppliers_by_locations

16. 8.824 85.264 ↓ 10.0 52,490 1

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

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

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=70.457..76.44 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.881 8.881 ↑ 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.427..8.881 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 29.093 305.842 ↑ 3.6 143,432 1

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

  • Buffers: shared hit=3438, temp read=656 written=854
21. 17.603 121.993 ↓ 5.3 27,740 1

Sort (cost=548.31..561.46 rows=5,259 width=86) (actual time=119.802..121.993 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. 8.620 104.390 ↓ 5.3 27,740 1

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

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

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

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

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

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

  • Buffers: shared hit=28
26. 8.571 154.756 ↓ 7.3 143,480 1

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

  • Buffers: shared hit=2645, temp read=459 written=461
27. 4.711 146.185 ↑ 1.7 11,809 1

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

  • Sort Key: _distinct_delivery_sites_with_labels."deliveryLocation"
  • Sort Method: quicksort Memory: 1133kB
  • Buffers: shared hit=2645, temp read=459 written=461
28. 141.474 141.474 ↑ 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=135.801..141.474 rows=11,809 loops=1)

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

CTE _distinct_companies

30. 280.074 4,619.227 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=320 read=75491, temp read=13484 written=13529
31. 3,377.774 4,339.153 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,798.983..4,339.153 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=320 read=75491, temp read=13484 written=13529
32. 961.379 961.379 ↑ 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=372.548..961.379 rows=2,533,234 loops=1)

  • Buffers: shared hit=320 read=75491
33.          

CTE _distinct_companies_with_borders

34. 1.577 4,630.128 ↑ 1.4 11,308 1

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

  • Buffers: shared hit=935 read=75491, temp read=13484 written=13529
35. 0.401 2.354 ↑ 9.7 63 1

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

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

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

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

  • Buffers: shared hit=591
38. 0.082 0.186 ↑ 1.0 473 1

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

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

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

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

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

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

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

  • Buffers: shared hit=320 read=75491, temp read=13484 written=13529
42. 87.360 461.784 ↑ 3.6 143,432 1

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

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

  • Buffers: shared hit=3438, temp read=656 written=1863
44. 126.831 4,759.914 ↓ 206.0 3,156,404 1

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

  • Sort Key: _distinct_companies_with_borders."supplier_extId"
  • Sort Method: quicksort Memory: 1011kB
  • Buffers: shared hit=935 read=75491, temp read=13484 written=13529
45. 4,633.083 4,633.083 ↑ 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,628.13..4,633.083 rows=11,308 loops=1)

  • Buffers: shared hit=935 read=75491, temp read=13484 written=13529