explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T5fe : New

Settings
# exclusive inclusive rows x rows loops node
1. 20.121 24,502.825 ↑ 21.2 14,282 1

Sort (cost=6,249,618.67..6,250,376.55 rows=303,151 width=448) (actual time=24,500.083..24,502.825 rows=14,282 loops=1)

  • Sort Key: ((btrim(replace((vi.date_installation)::text, regexp_replace((vi.date_installation)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text), ''::text)))::date)
  • Sort Method: quicksort Memory: 4,132kB
2.          

CTE _first_obe_installation

3. 80.063 948.372 ↓ 8.8 299,004 1

Nested Loop (cost=2,690.38..89,406.53 rows=33,800 width=528) (actual time=0.065..948.372 rows=299,004 loops=1)

4. 0.041 0.041 ↓ 4.0 4 1

Seq Scan on installation i_1 (cost=0.00..1.24 rows=1 width=520) (actual time=0.027..0.041 rows=4 loops=1)

  • Filter: (((installation_type)::text = 'first'::text) AND ((installer)::text = 'mobistar'::text))
  • Rows Removed by Filter: 15
5. 655.916 868.268 ↓ 2.2 74,751 4

Bitmap Heap Scan on vente_installation vi_1 (cost=2,690.38..89,067.29 rows=33,800 width=12) (actual time=58.819..217.067 rows=74,751 loops=4)

  • Recheck Cond: (id_installation = i_1.id_installation)
  • Heap Blocks: exact=86,997
6. 212.352 212.352 ↓ 2.5 83,353 4

Bitmap Index Scan on ix_relationship47 (cost=0.00..2,681.93 rows=33,800 width=0) (actual time=53.088..53.088 rows=83,353 loops=4)

  • Index Cond: (id_installation = i_1.id_installation)
7.          

CTE _extra_obe_installation

8. 21.977 527.658 ↓ 2.0 66,604 1

Nested Loop (cost=2,690.38..89,406.53 rows=33,800 width=528) (actual time=0.563..527.658 rows=66,604 loops=1)

9. 0.484 0.484 ↓ 7.0 7 1

Seq Scan on installation i_2 (cost=0.00..1.24 rows=1 width=520) (actual time=0.460..0.484 rows=7 loops=1)

  • Filter: ((((installation_type)::text = 'extra'::text) AND with_date) OR ((installation_type)::text = 'repair'::text))
  • Rows Removed by Filter: 12
10. 427.371 505.197 ↑ 3.6 9,515 7

Bitmap Heap Scan on vente_installation vi_2 (cost=2,690.38..89,067.29 rows=33,800 width=12) (actual time=12.597..72.171 rows=9,515 loops=7)

  • Recheck Cond: (id_installation = i_2.id_installation)
  • Heap Blocks: exact=47,629
11. 77.826 77.826 ↑ 3.5 9,573 7

Bitmap Index Scan on ix_relationship47 (cost=0.00..2,681.93 rows=33,800 width=0) (actual time=11.118..11.118 rows=9,573 loops=7)

  • Index Cond: (id_installation = i_2.id_installation)
12.          

CTE _all_installation

13. 80.508 3,542.300 ↓ 5.4 365,608 1

Append (cost=0.42..325,746.00 rows=67,600 width=545) (actual time=0.965..3,542.300 rows=365,608 loops=1)

14. 64.933 1,106.339 ↓ 2.0 66,604 1

Nested Loop (cost=0.42..162,535.00 rows=33,800 width=545) (actual time=0.963..1,106.339 rows=66,604 loops=1)

15. 575.178 575.178 ↓ 2.0 66,604 1

CTE Scan on _extra_obe_installation extra (cost=0.00..676.00 rows=33,800 width=524) (actual time=0.573..575.178 rows=66,604 loops=1)

16. 466.228 466.228 ↑ 1.0 1 66,604

Index Scan using vente_installation_pkey on vente_installation vi_3 (cost=0.42..4.78 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=66,604)

  • Index Cond: (id_vente_installation = extra.id_vente_installation)
17. 295.105 2,355.453 ↓ 8.8 299,004 1

Nested Loop (cost=0.42..162,535.00 rows=33,800 width=545) (actual time=0.101..2,355.453 rows=299,004 loops=1)

18. 1,163.336 1,163.336 ↓ 8.8 299,004 1

CTE Scan on _first_obe_installation first (cost=0.00..676.00 rows=33,800 width=524) (actual time=0.077..1,163.336 rows=299,004 loops=1)

19. 897.012 897.012 ↑ 1.0 1 299,004

Index Scan using vente_installation_pkey on vente_installation vi_4 (cost=0.42..4.78 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=299,004)

  • Index Cond: (id_vente_installation = first.id_vente_installation)
20. 1,404.927 24,482.704 ↑ 21.2 14,282 1

Hash Join (cost=1,253,181.59..5,656,323.70 rows=303,151 width=448) (actual time=23,340.583..24,482.704 rows=14,282 loops=1)

  • Hash Cond: (sa.id_address_manager = am.id_adresse_manager)
  • Join Filter: ((am.id_home_type <> 1) OR ((i.installer)::text <> 'telenet'::text) OR ((i.installation_type)::text <> 'first'::text))
21. 92.168 18,206.281 ↑ 21.3 14,282 1

Nested Loop Left Join (cost=1,218,660.53..5,401,776.11 rows=303,910 width=412) (actual time=17,870.968..18,206.281 rows=14,282 loops=1)

22. 67.702 18,071.267 ↑ 21.3 14,282 1

Hash Join (cost=1,218,651.46..1,388,018.51 rows=303,910 width=400) (actual time=17,870.848..18,071.267 rows=14,282 loops=1)

  • Hash Cond: (vi.id_vente = sa.id_sale)
23. 49.436 17,713.577 ↑ 21.3 14,282 1

Hash Right Join (cost=1,201,606.73..1,331,788.36 rows=303,910 width=404) (actual time=17,580.363..17,713.577 rows=14,282 loops=1)

  • Hash Cond: (t.id_objet = vi.id_vente_installation)
24. 1,004.320 1,004.320 ↓ 3.3 57,632 1

Seq Scan on ticket t (cost=0.00..113,831.05 rows=17,663 width=4) (actual time=0.112..1,004.320 rows=57,632 loops=1)

  • Filter: ((id_type_objet = 2) AND (id_ticketing_thematique = ANY ('{350,351,352,1054}'::integer[])))
  • Rows Removed by Filter: 1,897,990
25. 12.277 16,659.821 ↑ 21.3 14,277 1

Hash (cost=1,181,780.86..1,181,780.86 rows=303,910 width=404) (actual time=16,659.821..16,659.821 rows=14,277 loops=1)

  • Buckets: 4,096 Batches: 16 Memory Usage: 128kB
26. 10.251 16,647.544 ↑ 21.3 14,277 1

Hash Join (cost=1,165,151.03..1,181,780.86 rows=303,910 width=404) (actual time=16,327.822..16,647.544 rows=14,277 loops=1)

  • Hash Cond: (vi.id_installation = i.id_installation)
  • Join Filter: ((((i.installer)::text <> 'telenet'::text) OR ((i.installation_type)::text <> 'first'::text) OR ((tap.valeur)::text <> 'yes'::text)) AND (((i.installer)::text <> 'voo'::text) OR ((i.installation_type)::text <> 'first'::text) OR ((visit.valeur)::text <> 'SINGLE VISIT'::text)))
  • Rows Removed by Join Filter: 745
27. 66.716 16,637.247 ↑ 21.3 15,222 1

Merge Join (cost=1,165,149.64..1,172,965.88 rows=324,178 width=110) (actual time=16,327.021..16,637.247 rows=15,222 loops=1)

  • Merge Cond: (vi.id_vente = visit.id_vente)
28. 11.215 15,026.690 ↑ 10.2 15,222 1

Merge Left Join (cost=896,114.01..897,263.69 rows=155,872 width=90) (actual time=15,005.419..15,026.690 rows=15,222 loops=1)

  • Merge Cond: (v.id_vente = tap.id_vente)
29. 13.149 14,852.233 ↑ 10.2 15,222 1

Sort (cost=784,756.98..785,146.66 rows=155,872 width=74) (actual time=14,848.993..14,852.233 rows=15,222 loops=1)

  • Sort Key: v.id_vente
  • Sort Method: quicksort Memory: 2,525kB
30. 54.978 14,839.084 ↑ 10.2 15,222 1

Hash Join (cost=538,704.02..764,386.52 rows=155,872 width=74) (actual time=12,704.612..14,839.084 rows=15,222 loops=1)

  • Hash Cond: (v.id_client = c.id_client)
31. 159.031 13,732.026 ↑ 10.5 15,222 1

Hash Right Join (cost=471,701.07..688,137.23 rows=159,966 width=69) (actual time=11,652.329..13,732.026 rows=15,222 loops=1)

  • Hash Cond: (cablo.id_vente = v.id_vente)
32. 1,968.172 2,074.987 ↑ 1.1 440,052 1

Bitmap Heap Scan on vente_attribut cablo (cost=11,020.63..224,101.56 rows=468,154 width=20) (actual time=153.338..2,074.987 rows=440,052 loops=1)

  • Recheck Cond: (id_attribut = 79)
  • Heap Blocks: exact=158,543
33. 106.815 106.815 ↑ 1.0 457,501 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..10,903.59 rows=468,154 width=0) (actual time=106.815..106.815 rows=457,501 loops=1)

  • Index Cond: (id_attribut = 79)
34. 9.251 11,498.008 ↑ 10.1 15,222 1

Hash (cost=458,759.85..458,759.85 rows=153,647 width=53) (actual time=11,498.008..11,498.008 rows=15,222 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,127kB
35. 99.776 11,488.757 ↑ 10.1 15,222 1

Hash Right Join (cost=242,488.85..458,759.85 rows=153,647 width=53) (actual time=11,108.817..11,488.757 rows=15,222 loops=1)

  • Hash Cond: (single.id_vente = v.id_vente)
36. 289.119 357.818 ↑ 1.0 445,824 1

Bitmap Heap Scan on vente_attribut single (cost=10,872.00..223,874.49 rows=461,879 width=20) (actual time=73.300..357.818 rows=445,824 loops=1)

  • Recheck Cond: (id_attribut = 3,151)
  • Heap Blocks: exact=22,539
37. 68.699 68.699 ↓ 1.0 463,460 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..10,756.53 rows=461,879 width=0) (actual time=68.699..68.699 rows=463,460 loops=1)

  • Index Cond: (id_attribut = 3,151)
38. 10.112 11,031.163 ↑ 9.8 15,222 1

Hash (cost=229,747.06..229,747.06 rows=149,583 width=37) (actual time=11,031.163..11,031.163 rows=15,222 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,071kB
39. 54.992 11,021.051 ↑ 9.8 15,222 1

Hash Join (cost=18,930.37..229,747.06 rows=149,583 width=37) (actual time=1,696.411..11,021.051 rows=15,222 loops=1)

  • Hash Cond: (vi.id_vente = v.id_vente)
40. 10,608.866 10,608.866 ↑ 9.8 15,222 1

Seq Scan on vente_installation vi (cost=0.00..204,582.96 rows=149,583 width=29) (actual time=1,338.726..10,608.866 rows=15,222 loops=1)

  • Filter: ((date_installation IS NOT NULL) AND ((date_installation)::text ~* '^\d{4}-\d{2}-\d{2}'::text) AND ((btrim(replace((date_installation)::text, regexp_replace((date_installation)::text, '[^a-zA-Z]'::text, ''::text, 'g'::text), ''::text)))::date > (date_trunc('month'::text, now()) - '3 mons'::interval)))
  • Rows Removed by Filter: 566,300
41. 133.118 357.193 ↑ 1.0 449,436 1

Hash (cost=11,552.61..11,552.61 rows=449,661 width=8) (actual time=357.193..357.193 rows=449,436 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 8,787kB
42. 224.075 224.075 ↑ 1.0 449,436 1

Seq Scan on vente v (cost=0.00..11,552.61 rows=449,661 width=8) (actual time=0.606..224.075 rows=449,436 loops=1)

43. 156.904 1,052.080 ↓ 1.0 449,267 1

Hash (cost=59,393.31..59,393.31 rows=437,731 width=13) (actual time=1,052.080..1,052.080 rows=449,267 loops=1)

  • Buckets: 32,768 Batches: 2 Memory Usage: 10,559kB
44. 895.176 895.176 ↓ 1.0 449,267 1

Seq Scan on client c (cost=0.00..59,393.31 rows=437,731 width=13) (actual time=0.027..895.176 rows=449,267 loops=1)

45. 25.406 163.242 ↑ 1.2 36,633 1

Sort (cost=111,356.98..111,466.35 rows=43,746 width=20) (actual time=156.409..163.242 rows=36,633 loops=1)

  • Sort Key: tap.id_vente
  • Sort Method: quicksort Memory: 3,219kB
46. 126.974 137.836 ↑ 1.2 35,890 1

Bitmap Heap Scan on vente_attribut tap (cost=1,031.47..107,984.85 rows=43,746 width=20) (actual time=17.418..137.836 rows=35,890 loops=1)

  • Recheck Cond: (id_attribut = 3,090)
  • Heap Blocks: exact=33,212
47. 10.862 10.862 ↑ 1.2 37,150 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..1,020.53 rows=43,746 width=0) (actual time=10.862..10.862 rows=37,150 loops=1)

  • Index Cond: (id_attribut = 3,090)
48. 85.193 1,543.841 ↑ 1.0 386,511 1

Materialize (cost=269,035.63..271,062.62 rows=405,399 width=20) (actual time=1,321.364..1,543.841 rows=386,511 loops=1)

49. 591.505 1,458.648 ↑ 1.1 384,296 1

Sort (cost=269,035.63..270,049.12 rows=405,399 width=20) (actual time=1,321.334..1,458.648 rows=384,296 loops=1)

  • Sort Key: visit.id_vente
  • Sort Method: external merge Disk: 10,240kB
50. 760.894 867.143 ↑ 1.1 384,300 1

Bitmap Heap Scan on vente_attribut visit (cost=9,546.28..222,958.77 rows=405,399 width=20) (actual time=154.442..867.143 rows=384,300 loops=1)

  • Recheck Cond: (id_attribut = 206)
  • Heap Blocks: exact=156,954
51. 106.249 106.249 ↓ 1.1 437,287 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..9,444.93 rows=405,399 width=0) (actual time=106.249..106.249 rows=437,287 loops=1)

  • Index Cond: (id_attribut = 206)
52. 0.008 0.046 ↓ 1.2 18 1

Hash (cost=1.20..1.20 rows=15 width=298) (actual time=0.046..0.046 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
53. 0.038 0.038 ↓ 1.2 18 1

Seq Scan on installation i (cost=0.00..1.20 rows=15 width=298) (actual time=0.030..0.038 rows=18 loops=1)

  • Filter: ((installation)::text <> 'Do It Yourself'::text)
  • Rows Removed by Filter: 1
54. 129.281 289.988 ↑ 1.0 449,363 1

Hash (cost=9,430.36..9,430.36 rows=464,109 width=8) (actual time=289.988..289.988 rows=449,363 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 8,785kB
55. 160.707 160.707 ↑ 1.0 449,363 1

Seq Scan on sale_address sa (cost=0.00..9,430.36 rows=464,109 width=8) (actual time=0.053..160.707 rows=449,363 loops=1)

  • Filter: (id_type_address = 4)
56. 0.000 42.846 ↑ 1.0 1 14,282

Index Scan using vente_installation_meeting_pkey on vente_installation_meeting vim (cost=9.07..13.20 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=14,282)

  • Index Cond: (id_vente_installation_meeting = (SubPlan 6))
57.          

SubPlan (for Index Scan)

58. 14.282 71.410 ↑ 1.0 1 14,282

Aggregate (cost=8.63..8.64 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=14,282)

59. 57.128 57.128 ↑ 3.0 1 14,282

Index Scan using fki_vente_installation_meeting_id_vente_installation on vente_installation_meeting (cost=0.42..8.63 rows=3 width=4) (actual time=0.004..0.004 rows=1 loops=14,282)

  • Index Cond: (id_vente_installation = vi.id_vente_installation)
60. 14.282 71.410 ↑ 1.0 1 14,282

Aggregate (cost=8.63..8.64 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=14,282)

61. 57.128 57.128 ↑ 3.0 1 14,282

Index Scan using fki_vente_installation_meeting_id_vente_installation on vente_installation_meeting (cost=0.42..8.63 rows=3 width=4) (actual time=0.004..0.004 rows=1 loops=14,282)

  • Index Cond: (id_vente_installation = vi.id_vente_installation)
62. 343.316 808.409 ↑ 1.0 607,824 1

Hash (cost=17,816.42..17,816.42 rows=622,042 width=48) (actual time=808.409..808.409 rows=607,824 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 12,532kB
63. 465.093 465.093 ↑ 1.0 607,824 1

Seq Scan on adresse_manager am (cost=0.00..17,816.42 rows=622,042 width=48) (actual time=0.010..465.093 rows=607,824 loops=1)

64.          

SubPlan (for Hash Join)

65. 3,959.493 3,959.493 ↓ 5.3 350,062 1

CTE Scan on _all_installation (cost=0.00..1,605.50 rows=66,586 width=4) (actual time=0.987..3,959.493 rows=350,062 loops=1)

  • Filter: ((installation)::text <> ALL ('{"Repair Duo","Repair VOO","Repair Telenet"}'::text[]))
  • Rows Removed by Filter: 15,546
66. 103.594 103.594 ↓ 4.8 320,833 1

CTE Scan on _all_installation _all_installation_1 (cost=0.00..1,521.00 rows=67,262 width=4) (actual time=0.044..103.594 rows=320,833 loops=1)

  • Filter: ((installation)::text <> 'Repair Orange'::text)
  • Rows Removed by Filter: 44,775
Planning time : 14.999 ms
Execution time : 24,531.651 ms