explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B51n

Settings
# exclusive inclusive rows x rows loops node
1. 431.486 5,227.023 ↑ 14.1 313,329 1

Merge Join (cost=598,487.67..1,395,238.42 rows=4,411,995 width=64) (actual time=4,608.202..5,227.023 rows=313,329 loops=1)

  • Buffers: shared hit=4502 read=75331, temp read=15382 written=16942
2.          

CTE _distinct_delivery_sites

3. 19.226 121.332 ↑ 1.2 11,613 1

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

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

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=86.217..102.106 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. 19.583 19.583 ↑ 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.571..19.583 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 1.942 129.712 ↑ 1.7 11,809 1

Merge Join (cost=1,884.7..2,183.98 rows=19,749 width=72) (actual time=127.204..129.712 rows=11,809 loops=1)

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

Sort (cost=661.51..663.03 rows=609 width=17) (actual time=1.043..1.091 rows=609 loops=1)

  • Sort Key: "Site"."extId"
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=615
9. 0.168 0.775 ↑ 1.0 609 1

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

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

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

  • Buffers: shared hit=591
11. 0.072 0.182 ↑ 1.0 473 1

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

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

Seq Scan on Site Site (cost=0..28.73 rows=473 width=13) (actual time=0.01..0.11 rows=473 loops=1)

  • Buffers: shared hit=24
13. 3.001 126.679 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=126.136..126.679 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. 123.678 123.678 ↑ 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=86.222..123.678 rows=11,613 loops=1)

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

CTE _distinct_suppliers_by_locations

16. 10.673 82.878 ↓ 10.0 52,490 1

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

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

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=65.02..72.205 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. 10.355 10.355 ↑ 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.611..10.355 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 57.781 349.863 ↑ 1.8 290,495 1

Merge Join (cost=2,233.33..10,034.27 rows=518,313 width=104) (actual time=268.567..349.863 rows=290,495 loops=1)

  • Buffers: shared hit=3407, temp read=853 written=1052
21. 41.396 138.566 ↓ 10.0 52,490 1

Sort (cost=429.31..442.43 rows=5,249 width=64) (actual time=132.052..138.566 rows=52,490 loops=1)

  • Sort Key: _distinct_suppliers_by_locations."deliveryLocation"
  • Sort Method: external merge Disk: 1576kB
  • Buffers: shared hit=765, temp read=394 written=591
22. 97.170 97.170 ↓ 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=65.027..97.17 rows=52,490 loops=1)

  • Buffers: shared hit=765, temp read=197 written=393
23. 20.578 153.516 ↓ 14.7 290,522 1

Sort (cost=1,804.02..1,853.39 rows=19,749 width=72) (actual time=136.509..153.516 rows=290,522 loops=1)

  • Sort Key: _distinct_delivery_sites_with_labels."deliveryLocation"
  • Sort Method: quicksort Memory: 1114kB
  • Buffers: shared hit=2642, temp read=459 written=461
24. 132.938 132.938 ↑ 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=72) (actual time=127.206..132.938 rows=11,809 loops=1)

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

CTE _distinct_companies

26. 267.442 3,996.503 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=480 read=75331, temp read=13484 written=13529
27. 3,222.060 3,729.061 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,200.377..3,729.061 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=480 read=75331, temp read=13484 written=13529
28. 507.001 507.001 ↑ 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=101.656..507.001 rows=2,533,234 loops=1)

  • Buffers: shared hit=480 read=75331
29.          

CTE _distinct_companies_with_borders

30. 4.303 4,009.520 ↑ 1.4 11,308 1

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

  • Buffers: shared hit=1095 read=75331, temp read=13484 written=13529
31. 0.247 0.916 ↑ 9.7 63 1

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

  • Sort Key: "Site_1"."extId"
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=615
32. 0.146 0.669 ↑ 1.0 609 1

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

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

  • Buffers: shared hit=591
34. 0.065 0.166 ↑ 1.0 473 1

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

  • Buffers: shared hit=24
35. 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.01..0.101 rows=473 loops=1)

  • Buffers: shared hit=24
36. 4.358 4,004.301 ↓ 1.1 11,308 1

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

  • Sort Key: _distinct_companies."company_extId"
  • Sort Method: quicksort Memory: 915kB
  • Buffers: shared hit=480 read=75331, temp read=13484 written=13529
37. 3,999.943 3,999.943 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=480 read=75331, temp read=13484 written=13529
38. 156.706 629.239 ↑ 1.8 290,495 1

Sort (cost=80,822.14..82,117.92 rows=518,313 width=72) (actual time=584.693..629.239 rows=290,495 loops=1)

  • Sort Key: _all_suppliers_by_delivery_sites."supplier_extId"
  • Sort Method: external merge Disk: 8360kB
  • Buffers: shared hit=3407, temp read=1898 written=3413
39. 472.533 472.533 ↑ 1.8 290,495 1

CTE Scan on _all_suppliers_by_delivery_sites _all_suppliers_by_delivery_sites (cost=0..10,366.26 rows=518,313 width=72) (actual time=268.572..472.533 rows=290,495 loops=1)

  • Buffers: shared hit=3407, temp read=853 written=2361
40. 149.754 4,166.298 ↓ 218.3 3,344,064 1

Sort (cost=1,371.57..1,409.88 rows=15,322 width=40) (actual time=4,023.493..4,166.298 rows=3,344,064 loops=1)

  • Sort Key: _distinct_companies_with_borders."supplier_extId"
  • Sort Method: quicksort Memory: 1011kB
  • Buffers: shared hit=1095 read=75331, temp read=13484 written=13529
41. 4,016.544 4,016.544 ↑ 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,003.905..4,016.544 rows=11,308 loops=1)

  • Buffers: shared hit=1095 read=75331, temp read=13484 written=13529