explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XmPL

Settings
# exclusive inclusive rows x rows loops node
1. 0.243 100,480.623 ↓ 2.5 48 1

Sort (cost=198,422.96..198,423.01 rows=19 width=889) (actual time=100,480.620..100,480.623 rows=48 loops=1)

  • Sort Key: relation_affaire.numero_metier
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=47934304 read=155118, temp read=315727 written=315863
2. 6,347.250 100,480.380 ↓ 2.5 48 1

Group (cost=198,420.46..198,422.55 rows=19 width=889) (actual time=84,313.701..100,480.380 rows=48 loops=1)

  • Group Key: 'BI003 CIP FIN DE MAINTIEN PREVUE'::text, (to_char(CURRENT_TIMESTAMP, 'yyyymmddhh24miss'::text)), (((date_trunc('day'::text, (CURRENT_DATE)::timestamp with time zone) - '1 day'::interval day))::character varying(10)), relation_affaire.id, (CASE WHEN ((relation_affaire.type_metier)::text = 'DOSSIER'::text) THEN 'Dossier'::character varying ELSE relation_affaire.type_metier END), ''::text, ''::text, (CASE WHEN ((relation_affaire.type_application)::text = 'ILIADE_GAS'::text) THEN 'Iliade GAS'::character varying WHEN ((relation_affaire.type_application)::text = 'ILIADE_GIA'::text) THEN 'Iliade GIA'::character varying ELSE relation_affaire.type_application END), (btrim(replace(concat(bi_partenaire.nom_raison_sociale, ' ', bi_partenaire.complement_raison_sociale, ' ', bi_partenaire.prenom), ' '::text, ' '::text))), 'STATUT'::text, code_texte.texte, 'MAINTIEN ENCLENCHE'::text, ''::text, 'DTE FIN MAINTIEN'::text, part_comm_dossier.dte_fin_maintien, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text, ''::text
  • Buffers: shared hit=47934304 read=155118, temp read=315727 written=315863
3. 31,293.889 94,133.130 ↓ 392,922.3 7,465,524 1

Sort (cost=198,420.46..198,420.51 rows=19 width=857) (actual time=84,313.687..94,133.130 rows=7,465,524 loops=1)

  • Sort Key: relation_affaire.id, (CASE WHEN ((relation_affaire.type_metier)::text = 'DOSSIER'::text) THEN 'Dossier'::character varying ELSE relation_affaire.type_metier END), (CASE WHEN ((relation_affaire.type_application)::text = 'ILIADE_GAS'::text) THEN 'Iliade GAS'::character varying WHEN ((relation_affaire.type_application)::text = 'ILIADE_GIA'::text) THEN 'Iliade GIA'::character varying ELSE relation_affaire.type_application END), (btrim(replace(concat(bi_partenaire.nom_raison_sociale, ' ', bi_partenaire.complement_raison_sociale, ' ', bi_partenaire.prenom), ' '::text, ' '::text))), code_texte.texte
  • Sort Method: external merge Disk: 1608600kB
  • Buffers: shared hit=47934304 read=155118, temp read=315727 written=315863
4. 22,833.885 62,839.241 ↓ 392,922.3 7,465,524 1

Nested Loop (cost=1,006.49..198,420.06 rows=19 width=857) (actual time=51.661..62,839.241 rows=7,465,524 loops=1)

  • Buffers: shared hit=47934304 read=155118
5. 308.752 35,254.568 ↓ 339,342.0 339,342 1

Nested Loop (cost=1,006.21..198,418.22 rows=1 width=99) (actual time=51.616..35,254.568 rows=339,342 loops=1)

  • Join Filter: (relation_affaire.metier_id = plan_partie.dossier_id)
  • Buffers: shared hit=40129438 read=155118
6. 0.000 33,517.000 ↓ 119,068.0 119,068 1

Nested Loop (cost=1,003.13..198,414.02 rows=1 width=123) (actual time=51.582..33,517.000 rows=119,068 loops=1)

  • Join Filter: ((relation_affaire.metier_id = mut_dossier.dossier_id) AND (dossier_gen.no_gen_dossier = (SubPlan 2)))
  • Rows Removed by Join Filter: 6445456
  • Buffers: shared hit=39205812 read=155118
7. 42.033 1,305.016 ↓ 228,980.0 228,980 1

Nested Loop (cost=1,002.70..198,354.02 rows=1 width=118) (actual time=1.877..1,305.016 rows=228,980 loops=1)

  • Buffers: shared hit=2055004 read=155118
8. 59.847 805.023 ↓ 228,980.0 228,980 1

Nested Loop (cost=1,002.27..198,353.02 rows=1 width=83) (actual time=1.868..805.023 rows=228,980 loops=1)

  • Buffers: shared hit=1139084 read=155118
9. 67.768 287.216 ↓ 228,980.0 228,980 1

Nested Loop (cost=1,001.85..198,352.04 rows=1 width=83) (actual time=1.859..287.216 rows=228,980 loops=1)

  • Join Filter: (relation_affaire.metier_id = part_dossier.dossier_id)
  • Buffers: shared hit=223164 read=155118
10. 0.000 7.768 ↓ 4,704.0 4,704 1

Gather (cost=1,001.29..198,333.16 rows=1 width=75) (actual time=1.838..7.768 rows=4,704 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=23366 read=155118
11. 0.232 1,420.449 ↓ 941.0 941 5 / 5

Nested Loop (cost=1.29..197,333.06 rows=1 width=75) (actual time=10.474..1,420.449 rows=941 loops=5)

  • Join Filter: (relation_affaire.metier_id = partie_dossier.dossier_id)
  • Buffers: shared hit=23366 read=155118
12. 0.253 1,413.813 ↓ 8.0 915 5 / 5

Nested Loop (cost=0.86..197,186.11 rows=114 width=71) (actual time=10.411..1,413.813 rows=915 loops=5)

  • Buffers: shared hit=5066 read=155118
13. 0.082 1,411.207 ↓ 2.4 22 5 / 5

Nested Loop (cost=0.43..197,062.27 rows=9 width=59) (actual time=10.340..1,411.207 rows=22 loops=5)

  • Buffers: shared hit=1109 read=155118
14. 1,410.614 1,410.614 ↑ 2.4 22 5 / 5

Parallel Seq Scan on part_comm_dossier (cost=0.00..196,812.73 rows=53 width=8) (actual time=10.236..1,410.614 rows=22 loops=5)

  • Filter: (dte_fin_maintien = (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) + '1 mon'::interval))
  • Rows Removed by Filter: 1462556
  • Buffers: shared hit=551 read=155118
15. 0.511 0.511 ↑ 1.0 1 111 / 5

Index Scan using relation_affaire_metier_id_produit_id_type_application_type_idx on relation_affaire (cost=0.43..4.70 rows=1 width=51) (actual time=0.022..0.023 rows=1 loops=111)

  • Index Cond: (metier_id = part_comm_dossier.dossier_id)
  • Filter: (societe_id = 5)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=558
16. 2.353 2.353 ↓ 1.1 41 111 / 5

Index Scan using idx_dossier_gen_dossier_id_no_gen_dossier on dossier_gen (cost=0.43..13.38 rows=38 width=12) (actual time=0.016..0.106 rows=41 loops=111)

  • Index Cond: (dossier_id = relation_affaire.metier_id)
  • Buffers: shared hit=3957
17. 6.404 6.404 ↑ 1.0 1 4,574 / 5

Index Scan using d_g_in_pi_d_fk on partie_dossier (cost=0.43..1.28 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=4,574)

  • Index Cond: (dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: (dossier_gen.dossier_id = dossier_id)
  • Buffers: shared hit=18300
18. 211.680 211.680 ↑ 1.1 49 4,704

Index Scan using idx_part_dossier_id_no_gen_dossier_role on part_dossier (cost=0.56..18.23 rows=52 width=8) (actual time=0.008..0.045 rows=49 loops=4,704)

  • Index Cond: ((dossier_id = partie_dossier.dossier_id) AND ((role)::text = 'AFFI'::text))
  • Buffers: shared hit=199798
19. 457.960 457.960 ↑ 1.0 1 228,980

Index Scan using p_part on part (cost=0.42..0.98 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=228,980)

  • Index Cond: (part_id = part_dossier.part_dossier_part_id)
  • Buffers: shared hit=915920
20. 457.960 457.960 ↑ 1.0 1 228,980

Index Scan using bi_partenaire_uk1 on bi_partenaire (cost=0.42..1.00 rows=1 width=43) (actual time=0.002..0.002 rows=1 loops=228,980)

  • Index Cond: (numero_contact = part.numero_contact)
  • Buffers: shared hit=915920
21. 6,640.420 6,640.420 ↓ 1.2 29 228,980

Index Scan using idx_mut_dossier_dossier_id_no_gen_dossier on mut_dossier (cost=0.43..12.72 rows=25 width=9) (actual time=0.003..0.029 rows=29 loops=228,980)

  • Index Cond: (dossier_id = partie_dossier.dossier_id)
  • Filter: (((statut)::text = ANY ('{ACTI,MIXT}'::text[])) AND ((etat)::text = 'VALI'::text))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=10892712
22.          

SubPlan (for Nested Loop)

23. 6,564.524 26,258.096 ↑ 1.0 1 6,564,524

Result (cost=1.87..1.88 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=6,564,524)

  • Buffers: shared hit=26258096
24.          

Initplan (for Result)

25. 0.000 19,693.572 ↑ 1.0 1 6,564,524

Limit (cost=0.43..1.87 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=6,564,524)

  • Buffers: shared hit=26258096
26. 19,693.572 19,693.572 ↑ 30.0 1 6,564,524

Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on mut_dossier a (cost=0.43..43.43 rows=30 width=4) (actual time=0.003..0.003 rows=1 loops=6,564,524)

  • Index Cond: ((dossier_id = mut_dossier.dossier_id) AND (no_gen_dossier IS NOT NULL))
  • Filter: ((etat)::text = 'VALI'::text)
  • Buffers: shared hit=26258096
27. 119.068 1,428.816 ↓ 3.0 3 119,068

Bitmap Heap Scan on plan_partie (cost=3.08..4.19 rows=1 width=8) (actual time=0.012..0.012 rows=3 loops=119,068)

  • Recheck Cond: ((partie_dossier_id = partie_dossier.partie_dossier_id) AND (dossier_id = partie_dossier.dossier_id))
  • Heap Blocks: exact=131794
  • Buffers: shared hit=923626
28. 119.068 1,309.748 ↓ 0.0 0 119,068

BitmapAnd (cost=3.08..3.08 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=119,068)

  • Buffers: shared hit=791832
29. 238.136 238.136 ↑ 7.0 3 119,068

Bitmap Index Scan on idx4fk_f_pi_in_p_pi (cost=0.00..0.89 rows=21 width=0) (actual time=0.002..0.002 rows=3 loops=119,068)

  • Index Cond: (partie_dossier_id = partie_dossier.partie_dossier_id)
  • Buffers: shared hit=357204
30. 952.544 952.544 ↑ 1.0 161 119,068

Bitmap Index Scan on idx_plan_partie_dossier_id (cost=0.00..1.94 rows=162 width=0) (actual time=0.008..0.008 rows=161 loops=119,068)

  • Index Cond: (dossier_id = partie_dossier.dossier_id)
  • Buffers: shared hit=434628
31. 4,750.788 4,750.788 ↓ 11.0 22 339,342

Index Scan using idx_code_texte_code_cle_code_type on code_texte (cost=0.28..1.11 rows=2 width=22) (actual time=0.005..0.014 rows=22 loops=339,342)

  • Index Cond: ((code_cle)::text = (mut_dossier.statut)::text)
  • Buffers: shared hit=7804866
Planning time : 13.461 ms
Execution time : 100,770.369 ms