explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cIBs

Settings
# exclusive inclusive rows x rows loops node
1. 120.292 2,054,120.935 ↓ 24,232.0 24,232 1

Hash Join (cost=193,652.78..215,655.37 rows=1 width=623) (actual time=2,053,817.931..2,054,120.935 rows=24,232 loops=1)

  • Hash Cond: (a1_bi_partenaire_ili_dos_affi.numero_contact = a1_part_ili_dos_affi.numero_contact)
  • Buffers: shared hit=353,075,228 read=1,169
2. 37.500 37.500 ↓ 1.0 374,273 1

Seq Scan on bi_partenaire a1_bi_partenaire_ili_dos_affi (cost=0.00..20,596.87 rows=374,187 width=21) (actual time=0.025..37.500 rows=374,273 loops=1)

  • Buffers: shared hit=16,855
3. 97.457 2,053,817.751 ↓ 24,232.0 24,232 1

Hash (cost=193,652.77..193,652.77 rows=1 width=111) (actual time=2,053,817.751..2,053,817.751 rows=24,232 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,278kB
  • Buffers: shared hit=352,985,674 read=1,169
4. 77.125 2,053,720.294 ↓ 24,232.0 24,232 1

Nested Loop (cost=3.95..193,652.77 rows=1 width=111) (actual time=0.826..2,053,720.294 rows=24,232 loops=1)

  • Buffers: shared hit=352,985,674 read=1,169
5. 112.255 2,053,497.777 ↓ 24,232.0 24,232 1

Nested Loop (cost=3.82..193,651.51 rows=1 width=111) (actual time=0.752..2,053,497.777 rows=24,232 loops=1)

  • Buffers: shared hit=352,937,209 read=1,169
6. 59.704 2,053,167.434 ↓ 24,232.0 24,232 1

Nested Loop Left Join (cost=3.68..193,649.14 rows=1 width=107) (actual time=0.713..2,053,167.434 rows=24,232 loops=1)

  • Buffers: shared hit=352,888,745 read=1,169
7. 67.635 5,818.746 ↓ 24,232.0 24,232 1

Nested Loop (cost=2.71..171,647.69 rows=1 width=58) (actual time=0.693..5,818.746 rows=24,232 loops=1)

  • Buffers: shared hit=3,180,717 read=1,169
8. 80.627 5,218.007 ↓ 37.1 24,232 1

Nested Loop (cost=2.29..169,595.32 rows=653 width=29) (actual time=0.612..5,218.007 rows=24,232 loops=1)

  • Buffers: shared hit=2,983,127 read=1,169
9. 45.681 4,895.060 ↓ 37.1 24,232 1

Nested Loop (cost=1.87..169,306.62 rows=653 width=29) (actual time=0.553..4,895.060 rows=24,232 loops=1)

  • Buffers: shared hit=2,886,140 read=1,152
10. 64.032 4,534.363 ↓ 36.5 24,232 1

Nested Loop (cost=1.43..168,722.97 rows=663 width=25) (actual time=0.516..4,534.363 rows=24,232 loops=1)

  • Buffers: shared hit=2,783,211 read=1,136
11. 133.739 4,228.011 ↓ 36.5 24,232 1

Nested Loop (cost=1.00..167,343.06 rows=663 width=25) (actual time=0.470..4,228.011 rows=24,232 loops=1)

  • Buffers: shared hit=2,686,199 read=1,089
12. 36.320 99.792 ↓ 7.9 57,064 1

Nested Loop (cost=0.57..5,708.72 rows=7,213 width=16) (actual time=0.321..99.792 rows=57,064 loops=1)

  • Buffers: shared hit=2,692 read=20
13. 0.324 0.324 ↑ 1.0 4 1

Index Scan using bi_prod_pkey on bi_prod (cost=0.14..24.70 rows=4 width=8) (actual time=0.252..0.324 rows=4 loops=1)

  • Filter: (fam_id = 27)
  • Rows Removed by Filter: 142
  • Buffers: shared hit=110
14. 63.148 63.148 ↑ 1.0 14,266 4

Index Scan using idx_dossier_pro_id on dossier (cost=0.42..1,274.75 rows=14,626 width=12) (actual time=0.027..15.787 rows=14,266 loops=4)

  • Index Cond: (pro_id = bi_prod.pro_id)
  • Buffers: shared hit=2,582 read=20
15. 1,513.180 3,994.480 ↓ 0.0 0 57,064

Index Scan using idx_mut_dossier_dossier_id_no_gen_dossier on mut_dossier (cost=0.43..22.40 rows=1 width=13) (actual time=0.069..0.070 rows=0 loops=57,064)

  • Index Cond: (dossier_id = dossier.dossier_id)
  • Filter: (((statut)::text <> 'TERM'::text) AND (no_gen_dossier = (SubPlan 3)))
  • Rows Removed by Filter: 11
  • Buffers: shared hit=2,683,507 read=1,069
16.          

SubPlan (for Index Scan)

17. 620.325 2,481.300 ↑ 1.0 1 620,325

Result (cost=0.53..0.54 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=620,325)

  • Buffers: shared hit=1,889,175
18.          

Initplan (for Result)

19. 0.000 1,860.975 ↑ 1.0 1 620,325

Limit (cost=0.43..0.53 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=620,325)

  • Buffers: shared hit=1,889,175
20. 1,860.975 1,860.975 ↑ 30.0 1 620,325

Index Only Scan Backward using olm_perf_i_2020_02097_1 on mut_dossier a (cost=0.43..3.41 rows=30 width=4) (actual time=0.003..0.003 rows=1 loops=620,325)

  • Index Cond: ((dossier_id = mut_dossier.dossier_id) AND (no_gen_dossier IS NOT NULL) AND (etat = 'VALI'::text))
  • Heap Fetches: 9,817
  • Buffers: shared hit=1,889,175
21. 242.320 242.320 ↑ 1.0 1 24,232

Index Scan using mu_d_in_d_g_fk on dossier_gen (cost=0.43..2.07 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=24,232)

  • Index Cond: (mut_dossier_id = mut_dossier.mut_dossier_id)
  • Buffers: shared hit=97,012 read=47
22. 315.016 315.016 ↑ 6.0 1 24,232

Index Scan using d_g_in_pt_d_fk on part_dossier a1_part_dossier_ili_affi (cost=0.43..0.82 rows=6 width=8) (actual time=0.009..0.013 rows=1 loops=24,232)

  • Index Cond: (dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: ((role)::text = 'AFFI'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=102,929 read=16
23. 242.320 242.320 ↑ 1.0 1 24,232

Index Scan using p_part on part a1_part_ili_dos_affi (cost=0.42..0.44 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=24,232)

  • Index Cond: (part_id = a1_part_dossier_ili_affi.part_dossier_part_id)
  • Buffers: shared hit=96,987 read=17
24. 384.664 533.104 ↑ 1.0 1 24,232

Index Scan using bi_adresse_formatee_numero_contact on bi_adresse_formatee a1_adresses_ili_dos_affi (cost=0.42..3.13 rows=1 width=29) (actual time=0.020..0.022 rows=1 loops=24,232)

  • Index Cond: (numero_contact = a1_part_ili_dos_affi.numero_contact)
  • Filter: (CASE WHEN ((type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((type_adresse)::text = 'LEGALE'::text) THEN 1 ELSE NULL::integer END = (SubPlan 4))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=197,590
25.          

SubPlan (for Index Scan)

26. 98.960 148.440 ↑ 1.0 1 24,740

Aggregate (cost=2.65..2.66 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=24,740)

  • Buffers: shared hit=100,062
27. 49.480 49.480 ↑ 1.0 1 24,740

Index Scan using bi_adresse_formatee_numero_contact on bi_adresse_formatee b (cost=0.42..2.64 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=24,740)

  • Index Cond: (a1_adresses_ili_dos_affi.numero_contact = numero_contact)
  • Buffers: shared hit=100,062
28. 253.038 2,047,288.984 ↑ 1.0 1 24,232

Nested Loop Left Join (cost=0.96..22,001.44 rows=1 width=57) (actual time=62.494..84.487 rows=1 loops=24,232)

  • Buffers: shared hit=349,708,028
29. 169.624 169.624 ↑ 1.0 1 24,232

Index Scan using d_g_in_pt_d_fk on part_dossier (cost=0.43..0.82 rows=1 width=8) (actual time=0.004..0.007 rows=1 loops=24,232)

  • Index Cond: (dossier_gen.dossier_gen_id = dossier_gen_id)
  • Filter: ((role)::text = 'CONS'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=102,945
30. 1,275,657.292 2,046,866.322 ↑ 1.0 1 20,737

Hash Right Join (cost=0.53..22,000.61 rows=1 width=57) (actual time=73.011..98.706 rows=1 loops=20,737)

  • Hash Cond: (a1_bi_partenaire_ili_dos_autre.numero_contact = part.numero_contact)
  • Buffers: shared hit=349,605,083
31. 770,897.975 770,897.975 ↓ 1.0 374,273 20,737

Seq Scan on bi_partenaire a1_bi_partenaire_ili_dos_autre (cost=0.00..20,596.87 rows=374,187 width=21) (actual time=0.001..37.175 rows=374,273 loops=20,737)

  • Buffers: shared hit=349,522,135
32. 41.474 311.055 ↑ 1.0 1 20,737

Hash (cost=0.52..0.52 rows=1 width=40) (actual time=0.015..0.015 rows=1 loops=20,737)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=82,948
33. 269.581 269.581 ↑ 1.0 1 20,737

Index Scan using p_part on part (cost=0.42..0.52 rows=1 width=40) (actual time=0.012..0.013 rows=1 loops=20,737)

  • Index Cond: (part_dossier.part_dossier_part_id = part_id)
  • Buffers: shared hit=82,948
34. 218.088 218.088 ↑ 1.0 1 24,232

Index Scan using bi_fam_prod_pkey on bi_fam_prod (cost=0.14..2.36 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=24,232)

  • Index Cond: (fam_id = 27)
  • Filter: (soc_id = 3)
  • Buffers: shared hit=48,464
35. 145.392 145.392 ↑ 1.0 1 24,232

Index Only Scan using bi_soc_pkey on bi_soc (cost=0.13..1.25 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=24,232)

  • Index Cond: (soc_id = 3)
  • Heap Fetches: 24,232
  • Buffers: shared hit=48,465
36.          

SubPlan (for Hash Join)

37. 145.392 145.392 ↑ 1.0 1 24,232

Index Scan using bi_code_pkey on bi_code bic (cost=0.28..2.50 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=24,232)

  • Index Cond: (((code_type)::text = 'TypeStatutDossier'::text) AND ((mut_dossier.statut)::text = (code_cle)::text))
  • Filter: ((cde_lan)::text = 'FR'::text)
  • Buffers: shared hit=72,696
Planning time : 12.566 ms
Execution time : 2,054,122.823 ms