explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ashp

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

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

  • Buffers: shared hit=4434 read=75427, temp read=15020 written=16276
2.          

CTE _distinct_delivery_sites

3. 17.821 114.835 ↑ 1.2 11,613 1

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

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

Sort (cost=17,997.4..18,341.94 rows=137,816 width=17) (actual time=81.986..97.014 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. 17.834 17.834 ↑ 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.523..17.834 rows=137,816 loops=1)

  • Buffers: shared hit=2027
6.          

CTE _distinct_delivery_sites_with_labels

7. 2.325 123.877 ↑ 1.7 11,809 1

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

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

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

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

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

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

  • Buffers: shared hit=591
11. 0.078 0.180 ↑ 1.0 473 1

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

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

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

  • Buffers: shared hit=24
13. 3.517 120.439 ↑ 1.2 11,809 1

Sort (cost=1,223.19..1,257.64 rows=13,782 width=64) (actual time=119.768..120.439 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. 116.922 116.922 ↑ 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=81.989..116.922 rows=11,613 loops=1)

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

CTE _distinct_suppliers_by_locations

16. 10.637 76.630 ↓ 10.0 52,490 1

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

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

Sort (cost=5,405.05..5,536.28 rows=52,490 width=20) (actual time=58.671..65.993 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. 7.664 7.664 ↑ 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.314..7.664 rows=52,490 loops=1)

  • Buffers: shared hit=765
19.          

CTE _all_suppliers_by_delivery_sites

20. 28.978 291.295 ↑ 3.6 143,432 1

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

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

Sort (cost=548.31..561.46 rows=5,259 width=86) (actual time=117.712..119.417 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.678 101.394 ↓ 5.3 27,740 1

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

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

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

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

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

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

  • Buffers: shared hit=28
26. 10.065 142.900 ↓ 7.3 143,480 1

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

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

Sort (cost=3,627.52..3,676.89 rows=19,749 width=190) (actual time=131.823..132.835 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. 127.753 127.753 ↑ 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=120.844..127.753 rows=11,809 loops=1)

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

CTE _distinct_companies

30. 279.228 4,252.719 ↓ 1.1 11,308 1

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

  • Buffers: shared hit=384 read=75427, temp read=13484 written=13529
31. 3,436.519 3,973.491 ↑ 1.0 2,533,234 1

Sort (cost=458,366.4..464,718.35 rows=2,540,778 width=14) (actual time=3,427.82..3,973.491 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=384 read=75427, temp read=13484 written=13529
32. 536.972 536.972 ↑ 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=118.996..536.972 rows=2,533,234 loops=1)

  • Buffers: shared hit=384 read=75427
33.          

CTE _distinct_companies_with_borders

34. 1.952 4,263.004 ↑ 1.4 11,308 1

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

  • Buffers: shared hit=999 read=75427, temp read=13484 written=13529
35. 0.252 1.040 ↑ 9.7 63 1

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

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

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

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

  • Buffers: shared hit=591
38. 0.065 0.161 ↑ 1.0 473 1

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

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

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

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

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

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

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

  • Buffers: shared hit=384 read=75427, temp read=13484 written=13529
42. 89.038 449.786 ↑ 3.6 143,432 1

Sort (cost=208,794.48..210,092.8 rows=519,331 width=308) (actual time=420.55..449.786 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. 360.748 360.748 ↑ 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=249.548..360.748 rows=143,432 loops=1)

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

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

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

  • Buffers: shared hit=999 read=75427, temp read=13484 written=13529