explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EZ5r

Settings
# exclusive inclusive rows x rows loops node
1. 3.493 927.977 ↓ 1.0 29,193 1

Hash Left Join (cost=74,577.35..75,371.83 rows=28,572 width=132) (actual time=914.690..927.977 rows=29,193 loops=1)

  • Hash Cond: (cor.id = cop_nr.cor_id)
2.          

CTE envoyer

3. 0.000 625.749 ↓ 1.7 101,100 1

Gather Merge (cost=52,078.18..59,091.23 rows=60,983 width=37) (actual time=582.004..625.749 rows=101,100 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 106.224 1,119.302 ↑ 1.2 50,550 2

Sort (cost=51,078.17..51,230.63 rows=60,983 width=37) (actual time=552.409..559.651 rows=50,550 loops=2)

  • Sort Key: com.correspondance, (concat_ws(' - '::text, lpad((com.no_commande)::text, 2, '0'::text), CASE WHEN ((array_to_string(array_agg(envoyer_nom.nom), '; '::text)) IS NULL) THEN (('('::text || (unite_administrative.acronyme)::text) || ')'::text) ELSE btrim(((((array_to_string(array_agg(envoyer_nom.nom), '; '::text)) || ' ('::text) || (unite_administrative.acronyme)::text) || ')'::text), ' '::text) END))
  • Sort Method: external merge Disk: 2856kB
5. 101.274 1,013.078 ↑ 1.2 50,550 2

Hash Join (cost=36,751.80..46,231.21 rows=60,983 width=37) (actual time=392.393..506.539 rows=50,550 loops=2)

  • Hash Cond: (com_1.envoyer_unite_administrative = unite_administrative.id)
6. 53.632 911.670 ↑ 1.2 50,550 2

Hash Left Join (cost=36,741.38..43,929.86 rows=60,983 width=45) (actual time=392.301..455.835 rows=50,550 loops=2)

  • Hash Cond: (com_1.id = envoyer_nom.commande)
7. 48.504 153.852 ↑ 1.2 50,550 2

Hash Join (cost=5,336.74..10,280.32 rows=60,983 width=17) (actual time=39.993..76.926 rows=50,550 loops=2)

  • Hash Cond: (com.id = com_1.id)
8. 26.682 26.682 ↑ 1.2 50,550 2

Parallel Seq Scan on commande com (cost=0.00..3,163.68 rows=60,983 width=13) (actual time=0.009..13.341 rows=50,550 loops=2)

  • Filter: actif
  • Rows Removed by Filter: 1080
9. 36.072 78.666 ↑ 1.0 103,260 2

Hash (cost=3,599.55..3,599.55 rows=105,855 width=8) (actual time=39.333..39.333 rows=103,260 loops=2)

  • Buckets: 131072 Batches: 2 Memory Usage: 3044kB
10. 42.594 42.594 ↑ 1.0 103,260 2

Seq Scan on commande com_1 (cost=0.00..3,599.55 rows=105,855 width=8) (actual time=0.007..21.297 rows=103,260 loops=2)

11. 43.548 704.186 ↑ 1.0 96,097 2

Hash (cost=29,432.25..29,432.25 rows=97,072 width=36) (actual time=352.093..352.093 rows=96,097 loops=2)

  • Buckets: 65536 Batches: 2 Memory Usage: 3114kB
12. 133.576 660.638 ↑ 1.0 96,097 2

GroupAggregate (cost=26,270.24..28,461.53 rows=97,072 width=36) (actual time=252.372..330.319 rows=96,097 loops=2)

  • Group Key: envoyer_nom.commande
13. 125.382 527.062 ↑ 1.0 96,626 2

Sort (cost=26,270.24..26,515.31 rows=98,027 width=36) (actual time=252.356..263.531 rows=96,626 loops=2)

  • Sort Key: envoyer_nom.commande
  • Sort Method: external merge Disk: 3008kB
14. 17.708 401.680 ↑ 1.0 96,626 2

Subquery Scan on envoyer_nom (cost=14,237.03..15,462.37 rows=98,027 width=36) (actual time=179.747..200.840 rows=96,626 loops=2)

15. 302.716 383.972 ↑ 1.0 96,626 2

Sort (cost=14,237.03..14,482.10 rows=98,027 width=36) (actual time=179.746..191.986 rows=96,626 loops=2)

  • Sort Key: (((utilisateur.nom || ', '::text) || utilisateur.prenom))
  • Sort Method: external merge Disk: 3152kB
16. 64.806 81.256 ↑ 1.0 96,626 2

Hash Join (cost=225.58..3,429.15 rows=98,027 width=36) (actual time=0.906..40.628 rows=96,626 loops=2)

  • Hash Cond: (cep.utilisateur = utilisateur.id)
17. 14.698 14.698 ↑ 1.0 96,626 2

Seq Scan on commande_envoye_personne cep (cost=0.00..1,476.27 rows=98,027 width=8) (actual time=0.013..7.349 rows=96,626 loops=2)

18. 0.790 1.752 ↑ 1.0 2,026 2

Hash (cost=200.26..200.26 rows=2,026 width=20) (actual time=0.876..0.876 rows=2,026 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 124kB
19. 0.962 0.962 ↑ 1.0 2,026 2

Seq Scan on utilisateur (cost=0.00..200.26 rows=2,026 width=20) (actual time=0.006..0.481 rows=2,026 loops=2)

20. 0.062 0.134 ↑ 1.2 131 2

Hash (cost=8.52..8.52 rows=152 width=12) (actual time=0.067..0.067 rows=131 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
21. 0.072 0.072 ↑ 1.2 131 2

Seq Scan on unite_administrative (cost=0.00..8.52 rows=152 width=12) (actual time=0.009..0.036 rows=131 loops=2)

22.          

CTE envoyer_tous

23. 53.715 726.598 ↓ 143.2 28,644 1

HashAggregate (cost=1,524.58..1,527.08 rows=200 width=36) (actual time=715.840..726.598 rows=28,644 loops=1)

  • Group Key: envoyer.cor_id
24. 672.883 672.883 ↓ 1.7 101,100 1

CTE Scan on envoyer (cost=0.00..1,219.66 rows=60,983 width=36) (actual time=582.006..672.883 rows=101,100 loops=1)

25.          

CTE envoyer_non_rep

26. 2.266 12.241 ↓ 11.8 2,361 1

HashAggregate (cost=1,372.12..1,374.62 rows=200 width=36) (actual time=11.788..12.241 rows=2,361 loops=1)

  • Group Key: envoyer_1.cor_id
27. 9.975 9.975 ↑ 10.5 2,903 1

CTE Scan on envoyer envoyer_1 (cost=0.00..1,219.66 rows=30,492 width=36) (actual time=0.052..9.975 rows=2,903 loops=1)

  • Filter: (NOT status_repondu)
  • Rows Removed by Filter: 98197
28.          

CTE copie

29. 3.222 106.096 ↑ 1.0 6,831 1

Sort (cost=8,526.68..8,543.92 rows=6,896 width=37) (actual time=105.643..106.096 rows=6,831 loops=1)

  • Sort Key: com_2.correspondance, (concat_ws(' - '::text, lpad((com_2.no_commande)::text, 2, '0'::text), (array_to_string(array_agg(copie_acronyme.copie_ua), '; '::text))))
  • Sort Method: quicksort Memory: 786kB
30. 14.370 102.874 ↑ 1.0 6,831 1

Hash Join (cost=3,109.66..8,087.01 rows=6,896 width=37) (actual time=64.447..102.874 rows=6,831 loops=1)

  • Hash Cond: (com_2.id = copie_acronyme.commande)
31. 24.088 24.088 ↑ 1.0 101,100 1

Seq Scan on commande com_2 (cost=0.00..3,599.55 rows=103,671 width=13) (actual time=0.013..24.088 rows=101,100 loops=1)

  • Filter: actif
  • Rows Removed by Filter: 2160
32. 1.163 64.416 ↑ 1.0 6,944 1

Hash (cost=3,021.65..3,021.65 rows=7,041 width=36) (actual time=64.416..64.416 rows=6,944 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 494kB
33. 5.080 63.253 ↑ 1.0 6,944 1

GroupAggregate (cost=2,781.45..2,951.24 rows=7,041 width=36) (actual time=57.670..63.253 rows=6,944 loops=1)

  • Group Key: copie_acronyme.commande
34. 2.798 58.173 ↑ 1.0 8,445 1

Sort (cost=2,781.45..2,802.84 rows=8,556 width=36) (actual time=57.661..58.173 rows=8,445 loops=1)

  • Sort Key: copie_acronyme.commande
  • Sort Method: quicksort Memory: 988kB
35. 0.800 55.375 ↑ 1.0 8,445 1

Subquery Scan on copie_acronyme (cost=2,115.68..2,222.63 rows=8,556 width=36) (actual time=54.141..55.375 rows=8,445 loops=1)

36. 17.868 54.575 ↑ 1.0 8,445 1

Sort (cost=2,115.68..2,137.07 rows=8,556 width=68) (actual time=54.139..54.575 rows=8,445 loops=1)

  • Sort Key: ((unite_administrative_1.acronyme)::citext)
  • Sort Method: quicksort Memory: 1022kB
37. 3.822 36.707 ↑ 1.0 8,445 1

Hash Join (cost=1,054.95..1,556.86 rows=8,556 width=68) (actual time=30.319..36.707 rows=8,445 loops=1)

  • Hash Cond: (ccu.unite_administrative = unite_administrative_1.id)
38. 2.019 32.831 ↑ 1.0 8,445 1

Hash Left Join (cost=1,044.53..1,310.57 rows=8,556 width=40) (actual time=30.256..32.831 rows=8,445 loops=1)

  • Hash Cond: ((ccu.commande = copie_ua.commande) AND (ccu.unite_administrative = copie_ua.unite_administrative))
39. 0.567 0.567 ↑ 1.0 8,445 1

Seq Scan on commande_copie_unite ccu (cost=0.00..135.56 rows=8,556 width=8) (actual time=0.005..0.567 rows=8,445 loops=1)

40. 1.273 30.245 ↓ 36.4 7,281 1

Hash (cost=1,041.53..1,041.53 rows=200 width=40) (actual time=30.245..30.245 rows=7,281 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 474kB
41. 0.626 28.972 ↓ 36.4 7,281 1

Subquery Scan on copie_ua (cost=1,036.53..1,041.53 rows=200 width=40) (actual time=25.479..28.972 rows=7,281 loops=1)

42. 7.184 28.346 ↓ 36.4 7,281 1

HashAggregate (cost=1,036.53..1,039.53 rows=200 width=40) (actual time=25.478..28.346 rows=7,281 loops=1)

  • Group Key: ccu_1.unite_administrative, ccu_1.commande
43. 1.018 21.162 ↓ 21.8 7,503 1

Unique (cost=1,027.07..1,030.51 rows=344 width=40) (actual time=19.681..21.162 rows=7,503 loops=1)

44. 9.927 20.144 ↓ 23.4 8,050 1

Sort (cost=1,027.07..1,027.93 rows=344 width=40) (actual time=19.679..20.144 rows=8,050 loops=1)

  • Sort Key: (((utilisateur_1.nom || ', '::text) || utilisateur_1.prenom)), ccu_1.unite_administrative, ccu_1.commande
  • Sort Method: quicksort Memory: 789kB
45. 2.562 10.217 ↓ 23.4 8,050 1

Hash Left Join (cost=531.08..1,012.58 rows=344 width=40) (actual time=4.565..10.217 rows=8,050 loops=1)

  • Hash Cond: (ccp.utilisateur = utilisateur_unite_administrative_complementaire.utilisateur)
  • Filter: ((ccp.utilisateur IS NULL) OR (utilisateur_unite_administrative_complementaire.unite_administrative = ccu_1.unite_administrative) OR (utilisateur_1.unite_administrative = ccu_1.unite_administrative))
  • Rows Removed by Filter: 4579
46. 2.453 7.615 ↓ 1.2 11,729 1

Hash Left Join (cost=526.53..866.15 rows=9,711 width=32) (actual time=4.514..7.615 rows=11,729 loops=1)

  • Hash Cond: (ccu_1.commande = ccp.commande)
47. 0.658 0.658 ↑ 1.0 8,445 1

Seq Scan on commande_copie_unite ccu_1 (cost=0.00..135.56 rows=8,556 width=8) (actual time=0.002..0.658 rows=8,445 loops=1)

48. 1.226 4.504 ↓ 1.0 7,393 1

Hash (cost=434.36..434.36 rows=7,373 width=28) (actual time=4.504..4.504 rows=7,393 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 511kB
49. 1.644 3.278 ↓ 1.0 7,393 1

Hash Left Join (cost=225.58..434.36 rows=7,373 width=28) (actual time=1.109..3.278 rows=7,393 loops=1)

  • Hash Cond: (ccp.utilisateur = utilisateur_1.id)
50. 0.540 0.540 ↓ 1.0 7,393 1

Seq Scan on commande_copie_personne ccp (cost=0.00..115.73 rows=7,373 width=8) (actual time=0.009..0.540 rows=7,393 loops=1)

51. 0.310 1.094 ↑ 1.0 2,026 1

Hash (cost=200.26..200.26 rows=2,026 width=24) (actual time=1.094..1.094 rows=2,026 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 132kB
52. 0.784 0.784 ↑ 1.0 2,026 1

Seq Scan on utilisateur utilisateur_1 (cost=0.00..200.26 rows=2,026 width=24) (actual time=0.004..0.784 rows=2,026 loops=1)

53. 0.019 0.040 ↓ 1.3 201 1

Hash (cost=2.58..2.58 rows=158 width=8) (actual time=0.040..0.040 rows=201 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
54. 0.021 0.021 ↓ 1.3 201 1

Seq Scan on utilisateur_unite_administrative_complementaire (cost=0.00..2.58 rows=158 width=8) (actual time=0.008..0.021 rows=201 loops=1)

55. 0.018 0.054 ↑ 1.2 131 1

Hash (cost=8.52..8.52 rows=152 width=12) (actual time=0.054..0.054 rows=131 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
56. 0.036 0.036 ↑ 1.2 131 1

Seq Scan on unite_administrative unite_administrative_1 (cost=0.00..8.52 rows=152 width=12) (actual time=0.008..0.036 rows=131 loops=1)

57.          

CTE copie_tous

58. 6.108 113.478 ↓ 27.2 5,433 1

HashAggregate (cost=172.40..174.90 rows=200 width=36) (actual time=112.445..113.478 rows=5,433 loops=1)

  • Group Key: copie.cor_id
59. 107.370 107.370 ↑ 1.0 6,831 1

CTE Scan on copie (cost=0.00..137.92 rows=6,896 width=36) (actual time=105.645..107.370 rows=6,831 loops=1)

60.          

CTE copie_non_rep

61. 0.167 0.671 ↓ 1.1 229 1

HashAggregate (cost=155.16..157.66 rows=200 width=36) (actual time=0.633..0.671 rows=229 loops=1)

  • Group Key: copie_1.cor_id
62. 0.504 0.504 ↑ 14.4 240 1

CTE Scan on copie copie_1 (cost=0.00..137.92 rows=3,448 width=36) (actual time=0.085..0.504 rows=240 loops=1)

  • Filter: (NOT status_repondu)
  • Rows Removed by Filter: 6591
63. 8.521 923.729 ↓ 1.0 29,193 1

Hash Right Join (cost=3,701.45..3,708.20 rows=28,572 width=100) (actual time=913.923..923.729 rows=29,193 loops=1)

  • Hash Cond: (cop.cor_id = cor.id)
64. 115.018 115.018 ↓ 27.2 5,433 1

CTE Scan on copie_tous cop (cost=0.00..4.00 rows=200 width=36) (actual time=112.448..115.018 rows=5,433 loops=1)

65. 9.174 800.190 ↓ 1.0 29,193 1

Hash (cost=3,344.30..3,344.30 rows=28,572 width=68) (actual time=800.190..800.190 rows=29,193 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3841kB
66. 6.201 791.016 ↓ 1.0 29,193 1

Hash Right Join (cost=3,337.55..3,344.30 rows=28,572 width=68) (actual time=784.264..791.016 rows=29,193 loops=1)

  • Hash Cond: (env_nr.cor_id = cor.id)
67. 12.810 12.810 ↓ 11.8 2,361 1

CTE Scan on envoyer_non_rep env_nr (cost=0.00..4.00 rows=200 width=36) (actual time=11.790..12.810 rows=2,361 loops=1)

68. 9.583 772.005 ↓ 1.0 29,193 1

Hash (cost=2,980.40..2,980.40 rows=28,572 width=36) (actual time=772.005..772.005 rows=29,193 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3841kB
69. 9.567 762.422 ↓ 1.0 29,193 1

Hash Right Join (cost=2,973.87..2,980.40 rows=28,572 width=36) (actual time=729.592..762.422 rows=29,193 loops=1)

  • Hash Cond: (env.cor_id = cor.id)
70. 739.261 739.261 ↓ 143.2 28,644 1

CTE Scan on envoyer_tous env (cost=0.00..4.00 rows=200 width=36) (actual time=715.843..739.261 rows=28,644 loops=1)

71. 4.061 13.594 ↓ 1.0 29,193 1

Hash (cost=2,616.72..2,616.72 rows=28,572 width=4) (actual time=13.594..13.594 rows=29,193 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1283kB
72. 9.533 9.533 ↓ 1.0 29,193 1

Seq Scan on correspondance cor (cost=0.00..2,616.72 rows=28,572 width=4) (actual time=0.017..9.533 rows=29,193 loops=1)

73. 0.028 0.755 ↓ 1.1 229 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=0.755..0.755 rows=229 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
74. 0.727 0.727 ↓ 1.1 229 1

CTE Scan on copie_non_rep cop_nr (cost=0.00..4.00 rows=200 width=36) (actual time=0.635..0.727 rows=229 loops=1)

Planning time : 4.772 ms
Execution time : 934.140 ms