explain.depesz.com

A tool for finding a real cause for slow queries.

Result: CQV

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 4.333 3,790.532 ↓ 2,518.0 2,518 1

Nested Loop Semi Join (cost=374,108.75..995,124.67 rows=1 width=61) (actual time=2,535.604..3,790.532 rows=2,518 loops=1)

2. 4.591 3,776.127 ↓ 2,518.0 2,518 1

Nested Loop Left Join (cost=374,108.75..995,116.39 rows=1 width=65) (actual time=2,535.595..3,776.127 rows=2,518 loops=1)

3. 7.169 3,741.320 ↓ 2,518.0 2,518 1

Nested Loop Left Join (cost=374,108.75..995,111.54 rows=1 width=69) (actual time=2,534.893..3,741.320 rows=2,518 loops=1)

  • Join Filter: ("Typeorient".id = "Orientstruct".typeorient_id)
4. 79.108 3,729.115 ↓ 2,518.0 2,518 1

Nested Loop Left Join (cost=374,108.75..995,110.46 rows=1 width=73) (actual time=2,534.551..3,729.115 rows=2,518 loops=1)

  • Join Filter: ("Structurereferente".id = "Orientstruct".structurereferente_id)
5. 3.187 3,592.093 ↓ 2,518.0 2,518 1

Nested Loop (cost=374,108.75..995,104.92 rows=1 width=77) (actual time=2,534.185..3,592.093 rows=2,518 loops=1)

6. 4.688 3,576.316 ↓ 2,518.0 2,518 1

Nested Loop (cost=374,108.75..995,096.63 rows=1 width=16) (actual time=2,533.457..3,576.316 rows=2,518 loops=1)

7. 3.670 3,556.520 ↓ 2,518.0 2,518 1

Nested Loop Left Join (cost=374,108.75..995,091.89 rows=1 width=24) (actual time=2,532.835..3,556.520 rows=2,518 loops=1)

8. 15.666 3,537.742 ↓ 2,518.0 2,518 1

Hash Join (cost=374,108.75..995,087.17 rows=1 width=24) (actual time=2,531.573..3,537.742 rows=2,518 loops=1)

  • Hash Cond: ("Personne".id = "Orientstruct".personne_id)
9. 113.400 3,506.233 ↓ 2.0 36,244 1

Hash Left Join (cost=373,591.67..994,502.09 rows=18,131 width=20) (actual time=2,134.622..3,506.233 rows=36,244 loops=1)

  • Hash Cond: ("Personne".id = "Prestation".personne_id)
10. 111.870 3,089.090 ↓ 2.0 36,244 1

Hash Left Join (cost=352,735.40..966,221.25 rows=18,131 width=20) (actual time=1,830.848..3,089.090 rows=36,244 loops=1)

  • Hash Cond: ("Personne".id = "Calculdroitrsa".personne_id)
11. 42.330 2,765.411 ↓ 2.0 36,244 1

Nested Loop Left Join (cost=343,079.75..954,980.51 rows=18,131 width=20) (actual time=1,613.333..2,765.411 rows=36,244 loops=1)

12. 40.698 2,143.177 ↓ 2.0 36,244 1

Merge Left Join (cost=343,079.75..719,537.69 rows=18,131 width=20) (actual time=1,612.773..2,143.177 rows=36,244 loops=1)

  • Merge Cond: ("Dossier".id = "Suiviinstruction".dossier_id)
13. 71.218 1,636.790 ↓ 2.0 36,244 1

Sort (cost=343,079.75..343,125.08 rows=18,131 width=20) (actual time=1,612.025..1,636.790 rows=36,244 loops=1)

  • Sort Key: "Dossier".id
  • Sort Method: external merge Disk: 1056kB
14. 163.560 1,565.572 ↓ 2.0 36,244 1

Hash Join (cost=336,124.23..341,797.33 rows=18,131 width=20) (actual time=1,337.401..1,565.572 rows=36,244 loops=1)

  • Hash Cond: ("Dossier".id = "Foyer".dossier_id)
15. 65.194 65.194 ↑ 1.0 202,021 1

Seq Scan on dossiers "Dossier" (cost=0.00..4,734.21 rows=202,021 width=4) (actual time=0.019..65.194 rows=202,021 loops=1)

16. 20.822 1,336.818 ↓ 2.0 36,244 1

Hash (cost=335,897.59..335,897.59 rows=18,131 width=16) (actual time=1,336.818..1,336.818 rows=36,244 loops=1)

17. 82.574 1,315.996 ↓ 2.0 36,244 1

Hash Join (cost=29,904.16..335,897.59 rows=18,131 width=16) (actual time=630.117..1,315.996 rows=36,244 loops=1)

  • Hash Cond: ("Personne".foyer_id = "Foyer".id)
18. 241.975 1,085.718 ↓ 2.0 36,244 1

Hash Join (cost=23,108.67..327,771.48 rows=18,131 width=12) (actual time=467.623..1,085.718 rows=36,244 loops=1)

  • Hash Cond: ("Dsp".personne_id = "Personne".id)
  • Join Filter: (SubPlan 1)
19. 17.651 17.651 ↑ 1.0 36,263 1

Seq Scan on dsps "Dsp" (cost=0.00..953.63 rows=36,263 width=8) (actual time=0.026..17.651 rows=36,263 loops=1)

  • Filter: (id IS NOT NULL)
20. 252.670 463.462 ↑ 1.0 575,674 1

Hash (cost=13,663.74..13,663.74 rows=575,674 width=8) (actual time=463.462..463.462 rows=575,674 loops=1)

21. 210.792 210.792 ↑ 1.0 575,674 1

Seq Scan on personnes "Personne" (cost=0.00..13,663.74 rows=575,674 width=8) (actual time=0.020..210.792 rows=575,674 loops=1)

22.          

SubPlan (forHash Join)

23. 72.526 362.630 ↑ 1.0 1 36,263

Limit (cost=8.28..8.29 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=36,263)

24. 145.052 290.104 ↑ 1.0 1 36,263

Sort (cost=8.28..8.29 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=36,263)

  • Sort Key: dsps.id
  • Sort Method: quicksort Memory: 17kB
25. 145.052 145.052 ↑ 1.0 1 36,263

Index Scan using dsps_personne_id_idx on dsps (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=36,263)

  • Index Cond: (personne_id = $0)
26. 85.221 147.704 ↑ 1.0 202,022 1

Hash (cost=3,480.22..3,480.22 rows=202,022 width=8) (actual time=147.704..147.704 rows=202,022 loops=1)

27. 62.483 62.483 ↑ 1.0 202,022 1

Seq Scan on foyers "Foyer" (cost=0.00..3,480.22 rows=202,022 width=8) (actual time=0.019..62.483 rows=202,022 loops=1)

28. 88.537 465.689 ↓ 1.3 29,239 1

Index Scan using suivisinstruction_dossier_id_idx on suivisinstruction "Suiviinstruction" (cost=0.00..376,290.71 rows=22,541 width=4) (actual time=0.742..465.689 rows=29,239 loops=1)

  • Filter: (SubPlan 2)
29.          

SubPlan (forIndex Scan)

30. 47.144 377.152 ↑ 1.0 1 47,144

Limit (cost=8.30..8.31 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=47,144)

31. 188.576 330.008 ↑ 2.0 1 47,144

Sort (cost=8.30..8.31 rows=2 width=4) (actual time=0.007..0.007 rows=1 loops=47,144)

  • Sort Key: suivisinstruction.id
  • Sort Method: quicksort Memory: 17kB
32. 141.432 141.432 ↑ 1.0 2 47,144

Index Scan using suivisinstruction_dossier_id_idx on suivisinstruction (cost=0.00..8.29 rows=2 width=4) (actual time=0.003..0.003 rows=2 loops=47,144)

  • Index Cond: (dossier_id = $2)
33. 253.717 579.904 ↑ 1.0 1 36,244

Index Scan using adressesfoyers_actuelle_rsa_idx on adressesfoyers "Adressefoyer" (cost=0.00..12.97 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=36,244)

  • Index Cond: (("Adressefoyer".foyer_id = "Foyer".id) AND ("Adressefoyer".rgadr = '01'::bpchar))
  • Filter: (SubPlan 3)
34.          

SubPlan (forIndex Scan)

35. 72.486 326.187 ↑ 1.0 1 36,243

Limit (cost=8.30..8.31 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=36,243)

36. 108.729 253.701 ↑ 1.0 1 36,243

Sort (cost=8.30..8.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=36,243)

  • Sort Key: adressesfoyers.dtemm
  • Sort Method: quicksort Memory: 17kB
37. 144.972 144.972 ↑ 1.0 1 36,243

Index Scan using adressesfoyers_actuelle_rsa_idx on adressesfoyers (cost=0.00..8.29 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=36,243)

  • Index Cond: ((foyer_id = $4) AND (rgadr = '01'::bpchar))
38. 121.141 211.809 ↑ 1.0 292,429 1

Hash (cost=4,857.29..4,857.29 rows=292,429 width=4) (actual time=211.809..211.809 rows=292,429 loops=1)

39. 90.668 90.668 ↑ 1.0 292,429 1

Seq Scan on calculsdroitsrsa "Calculdroitrsa" (cost=0.00..4,857.29 rows=292,429 width=4) (actual time=0.258..90.668 rows=292,429 loops=1)

40. 114.534 303.743 ↓ 1.1 275,314 1

Hash (cost=16,791.37..16,791.37 rows=247,752 width=4) (actual time=303.743..303.743 rows=275,314 loops=1)

41. 145.787 189.209 ↓ 1.1 275,314 1

Bitmap Heap Scan on prestations "Prestation" (cost=6,379.09..16,791.37 rows=247,752 width=4) (actual time=45.119..189.209 rows=275,314 loops=1)

  • Recheck Cond: ((natprest = 'RSA'::bpchar) AND (rolepers = ANY ('{DEM,CJT}'::bpchar[])))
42. 43.422 43.422 ↓ 1.1 275,314 1

Bitmap Index Scan on prestations_allocataire_rsa_idx (cost=0.00..6,317.15 rows=247,752 width=0) (actual time=43.422..43.422 rows=275,314 loops=1)

  • Index Cond: (natprest = 'RSA'::bpchar)
43. 1.148 15.843 ↓ 3,188.0 3,188 1

Hash (cost=517.07..517.07 rows=1 width=16) (actual time=15.843..15.843 rows=3,188 loops=1)

44. 2.218 14.695 ↓ 3,188.0 3,188 1

Bitmap Heap Scan on orientsstructs "Orientstruct" (cost=490.00..517.07 rows=1 width=16) (actual time=12.520..14.695 rows=3,188 loops=1)

  • Recheck Cond: ((origine = 'cohorte'::type_origineorientstruct) AND (date_impression >= '2012-01-01'::date) AND (date_impression <= '2012-05-03'::date) AND (typeorient_id = 3))
  • Filter: ((statut_orient)::text = 'Orienté'::text)
45. 0.046 12.477 ↓ 0.0 0 1

BitmapAnd (cost=490.00..490.00 rows=7 width=0) (actual time=12.477..12.477 rows=0 loops=1)

46. 0.995 0.995 ↑ 1.0 7,028 1

Bitmap Index Scan on orientsstructs_origine_idx (cost=0.00..133.86 rows=7,143 width=0) (actual time=0.995..0.995 rows=7,028 loops=1)

  • Index Cond: (origine = 'cohorte'::type_origineorientstruct)
47. 9.937 9.937 ↑ 1.0 8,252 1

Bitmap Index Scan on orientsstructs_date_impression_idx (cost=0.00..175.82 rows=8,354 width=0) (actual time=9.937..9.937 rows=8,252 loops=1)

  • Index Cond: ((date_impression >= '2012-01-01'::date) AND (date_impression <= '2012-05-03'::date))
48. 1.499 1.499 ↑ 1.0 9,512 1

Bitmap Index Scan on orientsstructs_typeorient_id_idx (cost=0.00..179.82 rows=9,538 width=0) (actual time=1.499..1.499 rows=9,512 loops=1)

  • Index Cond: (typeorient_id = 3)
49. 15.108 15.108 ↑ 1.0 1 2,518

Index Scan using situationsdossiersrsa_dossier_rsa_id_idx on situationsdossiersrsa "Situationdossierrsa" (cost=0.00..4.70 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=2,518)

  • Index Cond: ("Situationdossierrsa".dossier_id = "Dossier".id)
50. 15.108 15.108 ↑ 1.0 1 2,518

Index Scan using detailsdroitsrsa_dossier_rsa_id_idx on detailsdroitsrsa "Detaildroitrsa" (cost=0.00..4.73 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=2,518)

  • Index Cond: ("Detaildroitrsa".dossier_id = "Dossier".id)
  • Filter: ("Detaildroitrsa".oridemrsa = 'DEM'::bpchar)
51. 12.590 12.590 ↑ 1.0 1 2,518

Index Scan using pdfs_fk_value_idx on pdfs "Pdf" (cost=0.00..8.27 rows=1 width=61) (actual time=0.004..0.005 rows=1 loops=2,518)

  • Index Cond: ("Pdf".fk_value = "Orientstruct".id)
  • Filter: (("Pdf".modele)::text = 'Orientstruct'::text)
52. 57.914 57.914 ↑ 1.0 113 2,518

Seq Scan on structuresreferentes "Structurereferente" (cost=0.00..4.13 rows=113 width=4) (actual time=0.001..0.023 rows=113 loops=2,518)

53. 5.036 5.036 ↑ 1.0 1 2,518

Seq Scan on typesorients "Typeorient" (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=2,518)

  • Filter: ("Typeorient".id = 3)
54. 30.216 30.216 ↑ 1.0 1 2,518

Index Scan using adresses_pkey on adresses "Adresse" (cost=0.00..4.84 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=2,518)

  • Index Cond: ("Adresse".id = "Adressefoyer".adresse_id)
55. 10.072 10.072 ↑ 1.0 1 2,518

Index Scan using pdfs_fk_value_idx on pdfs (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,518)

  • Index Cond: (pdfs.fk_value = "Pdf".fk_value)
  • Filter: ((pdfs.modele)::text = 'Orientstruct'::text)