explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bOUK

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 135,976.693 ↓ 30.0 60 1

Subquery Scan on s (cost=51,179.22..51,179.40 rows=2 width=3,064) (actual time=135,976.406..135,976.693 rows=60 loops=1)

2. 0.042 135,976.668 ↓ 30.0 60 1

Unique (cost=51,179.22..51,179.37 rows=2 width=3,428) (actual time=135,976.402..135,976.668 rows=60 loops=1)

3. 0.249 135,976.626 ↓ 30.0 60 1

Sort (cost=51,179.22..51,179.23 rows=2 width=3,428) (actual time=135,976.400..135,976.626 rows=60 loops=1)

  • Sort Key: a.id_agence, a.nom_agence, (CASE WHEN (s_1.id_gestionnaire_ics IS NULL) THEN '000000'::character varying ELSE s_1.id_gestionnaire_ics END), (CASE WHEN (s_1.id_gestionnaire_ics IS NULL) THEN 'non affecté'::character varying ELSE g.nom_prenom_gest END), g.mail_1_gest, p.nom_prenom_pro, (CASE WHEN (s_1.flag_mandat = '-1'::integer) THEN 'Oui'::text ELSE 'Non'::text END), s_1.date_debut_mandat, s_1.code_mandat_gestion_ics, s_1.code_societe_ics, s_1.code_immeuble_ics, i.cp_immeuble, s_1.code_lot_ics, o.numero_lot, o.lib_type_lot, (CASE WHEN (s_1.flag_principal_calcule = '-1'::integer) THEN 'Oui'::text ELSE 'Non'::text END), s_1.statut_locatif_ics, s_1.code_bail_ics, s_1.date_debut_bail_ics, s_1.date_fin_bail_ics, (CASE WHEN (s_1.flag_corresp_gesloc = '-1'::integer) THEN 'Oui'::text ELSE 'Non'::text END), s_1.ref_interne_gesloc, s_1.statut_locatif_gesloc, s_1.qualification_statut_gesloc, s_1.motif_gain_lot, s_1.date_effet_lot, l.nom_prenom_loc, b.montant_loyer, r.nom_region
  • Sort Method: quicksort Memory: 53kB
4. 0.018 135,976.377 ↓ 30.0 60 1

Append (cost=1,075.07..51,179.21 rows=2 width=3,428) (actual time=5.139..135,976.377 rows=60 loops=1)

5. 73.845 135,976.357 ↓ 60.0 60 1

Gather (cost=1,075.07..51,179.19 rows=1 width=388) (actual time=5.138..135,976.357 rows=60 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.089 135,902.512 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=75.07..50,179.09 rows=1 width=388) (actual time=3,827.112..135,902.512 rows=20 loops=3)

7. 0.068 135,902.343 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=74.92..50,176.71 rows=1 width=262) (actual time=3,827.088..135,902.343 rows=20 loops=3)

8. 0.060 135,901.915 ↓ 20.0 20 3 / 3

Nested Loop (cost=74.50..50,174.07 rows=1 width=274) (actual time=3,827.039..135,901.915 rows=20 loops=3)

9. 0.077 135,901.715 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=74.23..50,171.58 rows=1 width=246) (actual time=3,827.019..135,901.715 rows=20 loops=3)

10. 0.072 135,901.318 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=73.80..50,171.10 rows=1 width=249) (actual time=3,826.985..135,901.318 rows=20 loops=3)

11. 0.082 135,900.866 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=73.38..50,168.46 rows=1 width=250) (actual time=3,826.945..135,900.866 rows=20 loops=3)

12. 0.123 135,900.084 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=72.83..50,165.69 rows=1 width=263) (actual time=3,826.890..135,900.084 rows=20 loops=3)

13. 6.661 135,899.621 ↓ 20.0 20 3 / 3

Nested Loop Left Join (cost=72.54..50,163.18 rows=1 width=220) (actual time=3,826.847..135,899.621 rows=20 loops=3)

  • Filter: (COALESCE(b.montant_loyer, '0'::numeric) = '0'::numeric)
  • Rows Removed by Filter: 658
14. 19.691 20.080 ↓ 339.0 678 3 / 3

Parallel Bitmap Heap Scan on tf_suivi_lots s_1 (cost=71.99..7,030.23 rows=2 width=232) (actual time=0.640..20.080 rows=678 loops=3)

  • Recheck Cond: (id_agence = 132)
  • Filter: ((flag_mandat = '-1'::integer) AND ((arc_version_archivage)::text = 'J-1'::text) AND (CASE WHEN (flag_principal_calcule = '-1'::integer) THEN 'Oui'::text ELSE 'Non'::text END = 'Oui'::text) AND (date_entree_stock < ('now'::cstring)::date))
  • Rows Removed by Filter: 1,521
  • Heap Blocks: exact=812
15. 0.389 0.389 ↑ 1.0 6,599 1 / 3

Bitmap Index Scan on idx_suivilots_idagence (cost=0.00..71.99 rows=6,754 width=0) (actual time=1.167..1.168 rows=6,599 loops=1)

  • Index Cond: (id_agence = 132)
16. 135,872.880 135,872.880 ↑ 1.0 1 2,035 / 3

Index Scan using pk_itf_baux on itf_baux b (cost=0.55..21,566.46 rows=1 width=60) (actual time=169.184..200.304 rows=1 loops=2,035)

  • Index Cond: ((id_bail)::text = (s_1.id_bail_ics)::text)
  • Filter: (s_1.arc_date_archivage = arc_date_archivage)
17. 0.340 0.340 ↑ 1.0 1 60 / 3

Index Scan using pk_itr_gestionnaires on itr_gestionnaires g (cost=0.29..2.51 rows=1 width=62) (actual time=0.017..0.017 rows=1 loops=60)

  • Index Cond: ((id_gestionnaire)::text = (s_1.id_gestionnaire_ics)::text)
18. 0.700 0.700 ↑ 1.0 1 60 / 3

Index Scan using pk_itr_lots on itr_lots o (cost=0.56..2.77 rows=1 width=31) (actual time=0.035..0.035 rows=1 loops=60)

  • Index Cond: ((id_lot)::text = (s_1.id_lot_ics)::text)
19. 0.380 0.380 ↑ 1.0 1 60 / 3

Index Scan using pk_itr_proprietaires on itr_proprietaires p (cost=0.42..2.64 rows=1 width=37) (actual time=0.019..0.019 rows=1 loops=60)

  • Index Cond: ((id_proprietaire)::text = (s_1.id_proprietaire_ics)::text)
20. 0.320 0.320 ↑ 1.0 1 60 / 3

Index Scan using pk_itr_locataires on itr_locataires l (cost=0.43..0.48 rows=1 width=35) (actual time=0.016..0.016 rows=1 loops=60)

  • Index Cond: ((id_locataire)::text = (b.id_locataire)::text)
21. 0.140 0.140 ↑ 1.0 1 60 / 3

Index Scan using pk_tr_agences on tr_agences a (cost=0.27..2.49 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=60)

  • Index Cond: (id_agence = 132)
22. 0.360 0.360 ↑ 1.0 1 60 / 3

Index Scan using pk_itr_ensimmo on itr_immeubles i (cost=0.42..2.64 rows=1 width=22) (actual time=0.018..0.018 rows=1 loops=60)

  • Index Cond: ((id_immeuble)::text = (s_1.id_immeuble_ics)::text)
23. 0.080 0.080 ↑ 1.0 1 60 / 3

Index Scan using pk_tr_regions on tr_regions r (cost=0.15..2.37 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=60)

  • Index Cond: (id_region = a.id_region)
24. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=388) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: false
Planning time : 17.822 ms
Execution time : 135,976.981 ms