explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ajGO

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 5,554.440 ↑ 5.3 39 1

Limit (cost=2,856,887.90..2,940,897.00 rows=205 width=594) (actual time=59.788..5,554.440 rows=39 loops=1)

  • Output: gd.id, gd.libelle, uni.libelle, gd.date_creation, gd.date_modification, uni.id, ('HIERAR'), uni.hierarchie
  • Buffers: shared hit=71533 read=1722, temp read=44527 written=64610
2.          

CTE uniterec

3. 644.283 4,447.666 ↑ 18.9 1,856,011 1

Recursive Union (cost=2,721.20..2,856,828.17 rows=35,018,799 width=75) (actual time=28.508..4,447.666 rows=1,856,011 loops=1)

  • Buffers: shared hit=68949 read=1675, temp read=44527 written=45535
4. 45.556 125.674 ↑ 1.0 68,056 1

Hash Left Join (cost=2,721.20..9,397.35 rows=68,529 width=43) (actual time=28.506..125.674 rows=68,056 loops=1)

  • Output: uni_1.id, gd_1.id, uni_1.libelle, uni_1.code, (uni_1.id)::text, uni_1.id_ref_commune
  • Hash Cond: (cuni.id_unite = uni_1.id)
  • Buffers: shared hit=61 read=1628, temp read=551 written=543
5. 32.667 61.673 ↑ 1.0 68,056 1

Hash Join (cost=987.23..5,061.82 rows=68,529 width=16) (actual time=9.973..61.673 rows=68,056 loops=1)

  • Output: gd_1.id, cuni.id_unite
  • Hash Cond: (cuni.id_groupe_destinataires = gd_1.id)
  • Buffers: shared hit=6 read=1127, temp read=189 written=187
6. 19.116 19.116 ↑ 1.0 68,056 1

Seq Scan on rens.contenu_groupe_uni cuni (cost=0.00..2,185.99 rows=68,529 width=16) (actual time=0.004..19.116 rows=68,056 loops=1)

  • Output: cuni.id_groupe_destinataires, cuni.id_unite
  • Filter: cuni.hierarchie
  • Rows Removed by Filter: 65443
  • Buffers: shared hit=4 read=847
7. 4.905 9.890 ↑ 1.0 26,677 1

Hash (cost=548.77..548.77 rows=26,677 width=8) (actual time=9.890..9.890 rows=26,677 loops=1)

  • Output: gd_1.id
  • Buckets: 4096 Batches: 2 Memory Usage: 526kB
  • Buffers: shared hit=2 read=280, temp written=45
8. 4.985 4.985 ↑ 1.0 26,677 1

Seq Scan on rens.groupe_destinataires gd_1 (cost=0.00..548.77 rows=26,677 width=8) (actual time=0.002..4.985 rows=26,677 loops=1)

  • Output: gd_1.id
  • Buffers: shared hit=2 read=280
9. 8.902 18.445 ↑ 1.0 38,843 1

Hash (cost=944.43..944.43 rows=38,843 width=35) (actual time=18.445..18.445 rows=38,843 loops=1)

  • Output: uni_1.id, uni_1.libelle, uni_1.code, uni_1.id_ref_commune
  • Buckets: 2048 Batches: 4 Memory Usage: 586kB
  • Buffers: shared hit=55 read=501, temp written=173
10. 9.543 9.543 ↑ 1.0 38,843 1

Seq Scan on rens.unite uni_1 (cost=0.00..944.43 rows=38,843 width=35) (actual time=0.004..9.543 rows=38,843 loops=1)

  • Output: uni_1.id, uni_1.libelle, uni_1.code, uni_1.id_ref_commune
  • Buffers: shared hit=55 read=501
11. 969.528 3,677.709 ↑ 13.7 255,422 7

Merge Join (cost=122,300.47..214,705.48 rows=3,495,027 width=75) (actual time=277.715..525.387 rows=255,422 loops=7)

  • Output: unite.id, uniterec.groupid, unite.libelle, unite.code, ((uniterec.hierarchie || ','::text) || (unite.id)::text), unite.id_ref_commune
  • Merge Cond: (unite.id_unite_parente = uniterec.id)
  • Buffers: shared hit=68888 read=47, temp read=43976 written=25941
12. 62.657 62.657 ↑ 2.3 16,735 7

Index Scan using ixfk_unite_unite on rens.unite (cost=0.29..3,219.30 rows=38,843 width=43) (actual time=0.017..8.951 rows=16,735 loops=7)

  • Output: unite.id, unite.code, unite.libelle, unite.date_debut_effet, unite.date_fin_effet, unite.id_unite_parente, unite.id_ref_commune, unite.email, unite.ancien_code, unite.telephone, unite.niveau, unite.bmin, unite.bmax
  • Buffers: shared hit=68888 read=47
13. 312.214 2,645.524 ↑ 1.6 438,690 7

Materialize (cost=122,300.18..125,726.63 rows=685,290 width=48) (actual time=276.470..377.932 rows=438,690 loops=7)

  • Output: uniterec.groupid, uniterec.hierarchie, uniterec.id
  • Buffers: temp read=43976 written=25941
14. 1,973.286 2,333.310 ↑ 2.9 239,685 7

Sort (cost=122,300.18..124,013.40 rows=685,290 width=48) (actual time=273.882..333.330 rows=239,685 loops=7)

  • Output: uniterec.groupid, uniterec.hierarchie, uniterec.id
  • Sort Key: uniterec.id
  • Sort Method: quicksort Memory: 281kB
  • Buffers: temp read=43976 written=25941
15. 360.024 360.024 ↑ 2.6 265,144 7

WorkTable Scan on uniterec (cost=0.00..13,705.80 rows=685,290 width=48) (actual time=0.018..51.432 rows=265,144 loops=7)

  • Output: uniterec.groupid, uniterec.hierarchie, uniterec.id
  • Buffers: temp read=19063 written=6
16.          

Initplan (forLimit)

17. 0.032 0.032 ↑ 1.0 1 1

Seq Scan on rens.parametre (cost=0.00..2.88 rows=1 width=9) (actual time=0.025..0.032 rows=1 loops=1)

  • Output: (parametre.valeur)::bigint
  • Filter: ((parametre.code)::text = 'LIMITE_RECHERCHE_DESTINATAIRE'::text)
  • Rows Removed by Filter: 69
  • Buffers: shared read=2
18. 5.455 5,554.390 ↑ 52.6 39 1

Nested Loop (cost=56.85..840,147.85 rows=2,050 width=594) (actual time=59.753..5,554.390 rows=39 loops=1)

  • Output: gd.id, gd.libelle, uni.libelle, gd.date_creation, gd.date_modification, uni.id, 'HIERAR', uni.hierarchie
  • Join Filter: ((unaccent((uni.libelle)::text) ~~* unaccent('%ANT%'::text)) OR (unaccent((uni.code)::text) ~~* unaccent('%ANT%'::text)) OR (unaccent((dep.code)::text) ~~* unaccent('%ANT%'::text)) OR (unaccent((dep.libelle)::text) ~~* unaccent('%ANT%'::text)))
  • Rows Removed by Join Filter: 480
  • Buffers: shared hit=71533 read=1720, temp read=44527 written=64610
19. 0.467 5,548.416 ↑ 30.4 519 1

Nested Loop (cost=56.71..836,952.19 rows=15,752 width=720) (actual time=44.719..5,548.416 rows=519 loops=1)

  • Output: gd.id, gd.libelle, gd.date_creation, gd.date_modification, uni.libelle, uni.id, uni.hierarchie, uni.code, com.id_ref_departement
  • Buffers: shared hit=70495 read=1720, temp read=44527 written=64610
20. 211.689 5,546.272 ↑ 28.2 559 1

Hash Join (cost=56.42..831,910.42 rows=15,752 width=720) (actual time=44.700..5,546.272 rows=559 loops=1)

  • Output: gd.id, gd.libelle, gd.date_creation, gd.date_modification, uni.libelle, uni.id, uni.hierarchie, uni.idrefcommune, uni.code
  • Hash Cond: (uni.groupid = gd.id)
  • Buffers: shared hit=68973 read=1685, temp read=44527 written=64610
21. 5,334.485 5,334.485 ↑ 18.9 1,856,011 1

CTE Scan on uniterec uni (cost=0.00..700,375.98 rows=35,018,799 width=690) (actual time=28.510..5,334.485 rows=1,856,011 loops=1)

  • Output: uni.id, uni.groupid, uni.libelle, uni.code, uni.hierarchie, uni.idrefcommune
  • Buffers: shared hit=68949 read=1675, temp read=44527 written=64610
22. 0.004 0.098 ↑ 1.1 11 1

Hash (cost=56.27..56.27 rows=12 width=54) (actual time=0.098..0.098 rows=11 loops=1)

  • Output: gd.id, gd.libelle, gd.date_creation, gd.date_modification, gd.id_utilisateur_modification, gd.id_unite_modification
  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared hit=24 read=10
23. 0.094 0.094 ↑ 1.1 11 1

Index Scan using pk_groupe_destinataires on rens.groupe_destinataires gd (cost=0.30..56.27 rows=12 width=54) (actual time=0.019..0.094 rows=11 loops=1)

  • Output: gd.id, gd.libelle, gd.date_creation, gd.date_modification, gd.id_utilisateur_modification, gd.id_unite_modification
  • Index Cond: (gd.id = ANY ('{658878,660768,660422,664029,665994,666304,660555,658878,653695,647024,640368,659162}'::integer[]))
  • Buffers: shared hit=24 read=10
24. 1.677 1.677 ↑ 1.0 1 559

Index Scan using pk_ref_commune on rens.ref_commune com (cost=0.29..0.31 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=559)

  • Output: com.id, com.code_insee, com.code_postal, com.libelle, com.date_debut_effet, com.date_fin_effet, com.id_ref_departement, com.id_ref_pays, com.source, com.geometrie, com.id_ref_type_competence_territoriale, com.id_unite_competente
  • Index Cond: (com.id = uni.idrefcommune)
  • Buffers: shared hit=1522 read=35
25. 0.519 0.519 ↑ 1.0 1 519

Index Scan using pk_ref_departement on rens.ref_departement dep (cost=0.14..0.16 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=519)

  • Output: dep.id, dep.code, dep.libelle, dep.date_debut_effet, dep.date_fin_effet, dep.id_ref_region, dep.fuseau_horaire
  • Index Cond: (dep.id = com.id_ref_departement)
  • Filter: ((dep.code IS NOT NULL) AND (dep.libelle IS NOT NULL))
  • Buffers: shared hit=1038