explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1D6K

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 5,870.789 ↑ 6.6 39 1

Limit (cost=2,856,897.02..2,941,050.25 rows=256 width=594) (actual time=89.642..5,870.789 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=72037 read=1218, temp read=44527 written=64610
2.          

CTE uniterec

3. 676.193 4,705.385 ↑ 18.9 1,856,011 1

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

  • Buffers: shared hit=69450 read=1174, temp read=44527 written=45535
4. 56.127 166.648 ↑ 1.0 68,056 1

Hash Left Join (cost=2,721.20..9,397.35 rows=68,529 width=43) (actual time=35.695..166.648 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=562 read=1127, temp read=551 written=543
5. 46.736 85.201 ↑ 1.0 68,056 1

Hash Join (cost=987.23..5,061.82 rows=68,529 width=16) (actual time=10.258..85.201 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. 28.290 28.290 ↑ 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.003..28.290 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. 5.041 10.175 ↑ 1.0 26,677 1

Hash (cost=548.77..548.77 rows=26,677 width=8) (actual time=10.175..10.175 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. 5.134 5.134 ↑ 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.003..5.134 rows=26,677 loops=1)

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

Hash (cost=944.43..944.43 rows=38,843 width=35) (actual time=25.320..25.320 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=556, temp written=173
10. 11.456 11.456 ↑ 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..11.456 rows=38,843 loops=1)

  • Output: uni_1.id, uni_1.libelle, uni_1.code, uni_1.id_ref_commune
  • Buffers: shared hit=556
11. 1,020.838 3,862.544 ↑ 13.7 255,422 7

Merge Join (cost=122,300.47..214,705.48 rows=3,495,027 width=75) (actual time=289.204..551.792 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. 69.391 69.391 ↑ 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..9.913 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. 326.067 2,772.315 ↑ 1.6 438,690 7

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

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

Sort (cost=122,300.18..124,013.40 rows=685,290 width=48) (actual time=285.293..349.464 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. 381.528 381.528 ↑ 2.6 265,144 7

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

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

Initplan (forLimit)

17. 0.017 0.017 ↑ 1.0 1 1

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

  • Output: (parametre.valeur)::bigint
  • Filter: ((parametre.code)::text = 'LIMITE_RECHERCHE_DESTINATAIRE'::text)
  • Rows Removed by Filter: 69
  • Buffers: shared hit=2
18. 6.474 5,870.755 ↑ 65.7 39 1

Nested Loop (cost=65.97..842,255.71 rows=2,562 width=594) (actual time=89.623..5,870.755 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=72035 read=1218, temp read=44527 written=64610
19. 0.501 5,863.243 ↑ 37.9 519 1

Nested Loop (cost=65.83..838,261.14 rows=19,690 width=720) (actual time=63.245..5,863.243 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=70997 read=1218, temp read=44527 written=64610
20. 221.690 5,860.506 ↑ 35.2 559 1

Hash Join (cost=65.54..831,958.92 rows=19,690 width=720) (actual time=63.213..5,860.506 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=69474 read=1184, temp read=44527 written=64610
21. 5,638.689 5,638.689 ↑ 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=35.700..5,638.689 rows=1,856,011 loops=1)

  • Output: uni.id, uni.groupid, uni.libelle, uni.code, uni.hierarchie, uni.idrefcommune
  • Buffers: shared hit=69450 read=1174, temp read=44527 written=64610
22. 0.010 0.127 ↑ 1.4 11 1

Hash (cost=65.35..65.35 rows=15 width=54) (actual time=0.127..0.127 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.117 0.117 ↑ 1.4 11 1

Index Scan using pk_groupe_destinataires on rens.groupe_destinataires gd (cost=0.30..65.35 rows=15 width=54) (actual time=0.024..0.117 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,658878,660768,660422,664029,665994,666304,660555,658878,653695,647024,640368,659162}'::integer[]))
  • Buffers: shared hit=24 read=10
24. 2.236 2.236 ↑ 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.004..0.004 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=1523 read=34
25. 1.038 1.038 ↑ 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.002..0.002 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