explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cdFy : old

Settings
# exclusive inclusive rows x rows loops node
1. 20.169 16,436.354 ↑ 21.2 14,282 1

Sort (cost=5,749,076.45..5,749,834.32 rows=303,151 width=449) (actual time=16,433.904..16,436.354 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,128kB
2. 918.106 16,416.185 ↑ 21.2 14,282 1

Hash Join (cost=1,249,720.47..5,659,304.54 rows=303,151 width=449) (actual time=15,292.095..16,416.185 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))
3. 88.130 14,767.998 ↑ 21.3 14,282 1

Nested Loop Left Join (cost=1,218,660.53..5,401,776.11 rows=303,910 width=413) (actual time=14,452.359..14,767.998 rows=14,282 loops=1)

4. 60.906 14,637.022 ↑ 21.3 14,282 1

Hash Join (cost=1,218,651.46..1,388,018.51 rows=303,910 width=401) (actual time=14,452.270..14,637.022 rows=14,282 loops=1)

  • Hash Cond: (vi.id_vente = sa.id_sale)
5. 47.160 14,321.021 ↑ 21.3 14,282 1

Hash Right Join (cost=1,201,606.73..1,331,788.36 rows=303,910 width=405) (actual time=14,196.829..14,321.021 rows=14,282 loops=1)

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

Seq Scan on ticket t (cost=0.00..113,831.05 rows=17,663 width=4) (actual time=0.072..904.893 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
7. 10.136 13,368.968 ↑ 21.3 14,277 1

Hash (cost=1,181,780.86..1,181,780.86 rows=303,910 width=405) (actual time=13,368.968..13,368.968 rows=14,277 loops=1)

  • Buckets: 4,096 Batches: 16 Memory Usage: 128kB
8. 8.240 13,358.832 ↑ 21.3 14,277 1

Hash Join (cost=1,165,151.03..1,181,780.86 rows=303,910 width=405) (actual time=13,074.836..13,358.832 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
9. 64.380 13,350.562 ↑ 21.3 15,222 1

Merge Join (cost=1,165,149.64..1,172,965.88 rows=324,178 width=110) (actual time=13,074.727..13,350.562 rows=15,222 loops=1)

  • Merge Cond: (vi.id_vente = visit.id_vente)
10. 16.429 11,804.224 ↑ 10.2 15,222 1

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

  • Merge Cond: (v.id_vente = tap.id_vente)
11. 11.728 11,657.836 ↑ 10.2 15,222 1

Sort (cost=784,756.98..785,146.66 rows=155,872 width=74) (actual time=11,655.577..11,657.836 rows=15,222 loops=1)

  • Sort Key: v.id_vente
  • Sort Method: quicksort Memory: 2,525kB
12. 50.598 11,646.108 ↑ 10.2 15,222 1

Hash Join (cost=538,704.02..764,386.52 rows=155,872 width=74) (actual time=10,634.138..11,646.108 rows=15,222 loops=1)

  • Hash Cond: (v.id_client = c.id_client)
13. 128.620 10,685.539 ↑ 10.5 15,222 1

Hash Right Join (cost=471,701.07..688,137.23 rows=159,966 width=69) (actual time=9,723.976..10,685.539 rows=15,222 loops=1)

  • Hash Cond: (cablo.id_vente = v.id_vente)
14. 882.961 976.253 ↑ 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=142.843..976.253 rows=440,052 loops=1)

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

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

  • Index Cond: (id_attribut = 79)
16. 8.833 9,580.666 ↑ 10.1 15,222 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,127kB
17. 102.919 9,571.833 ↑ 10.1 15,222 1

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

  • Hash Cond: (single.id_vente = v.id_vente)
18. 234.616 290.732 ↑ 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=64.908..290.732 rows=445,824 loops=1)

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

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

  • Index Cond: (id_attribut = 3,151)
20. 9.835 9,178.182 ↑ 9.8 15,222 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,071kB
21. 52.001 9,168.347 ↑ 9.8 15,222 1

Hash Join (cost=18,930.37..229,747.06 rows=149,583 width=37) (actual time=1,525.079..9,168.347 rows=15,222 loops=1)

  • Hash Cond: (vi.id_vente = v.id_vente)
22. 8,802.922 8,802.922 ↑ 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,211.354..8,802.922 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
23. 131.556 313.424 ↑ 1.0 449,436 1

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

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

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

25. 151.023 909.971 ↓ 1.0 449,267 1

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

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

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

27. 21.865 129.959 ↑ 1.2 36,633 1

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

  • Sort Key: tap.id_vente
  • Sort Method: quicksort Memory: 3,219kB
28. 98.632 108.094 ↑ 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=15.532..108.094 rows=35,890 loops=1)

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

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

  • Index Cond: (id_attribut = 3,090)
30. 81.829 1,481.958 ↑ 1.0 386,511 1

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

31. 683.901 1,400.129 ↑ 1.1 384,296 1

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

  • Sort Key: visit.id_vente
  • Sort Method: external merge Disk: 10,240kB
32. 625.164 716.228 ↑ 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=136.392..716.228 rows=384,300 loops=1)

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

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

  • Index Cond: (id_attribut = 206)
34. 0.006 0.030 ↓ 1.2 18 1

Hash (cost=1.20..1.20 rows=15 width=299) (actual time=0.030..0.030 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
35. 0.024 0.024 ↓ 1.2 18 1

Seq Scan on installation i (cost=0.00..1.20 rows=15 width=299) (actual time=0.017..0.024 rows=18 loops=1)

  • Filter: ((installation)::text <> 'Do It Yourself'::text)
  • Rows Removed by Filter: 1
36. 112.540 255.095 ↑ 1.0 449,363 1

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

  • Buckets: 65,536 Batches: 2 Memory Usage: 8,785kB
37. 142.555 142.555 ↑ 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.048..142.555 rows=449,363 loops=1)

  • Filter: (id_type_address = 4)
38. 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 1))
39.          

SubPlan (for Index Scan)

40. 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)

41. 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)
42. 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)

43. 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)
44. 332.982 730.081 ↑ 1.0 607,824 1

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

  • Buckets: 32,768 Batches: 4 Memory Usage: 12,532kB
45. 397.099 397.099 ↑ 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.007..397.099 rows=607,824 loops=1)

Planning time : 10.962 ms
Execution time : 16,443.831 ms