explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q9MU

Settings
# exclusive inclusive rows x rows loops node
1. 2.836 4,236,590.650 ↓ 106.2 9,136 1

Nested Loop (cost=219.99..1,026,742.65 rows=86 width=54) (actual time=4,101,669.365..4,236,590.650 rows=9,136 loops=1)

  • Buffers: shared hit=3,082,100,888 read=5,750,813, temp written=874,041
2. 0.036 0.036 ↑ 1.0 1 1

Index Only Scan using p_contrat on contrat (cost=0.28..2.50 rows=1 width=4) (actual time=0.033..0.036 rows=1 loops=1)

  • Index Cond: (contrat_id = 4,368)
  • Heap Fetches: 1
  • Buffers: shared hit=4
3. 17.875 4,236,587.778 ↓ 106.2 9,136 1

Nested Loop (cost=219.71..1,026,739.29 rows=86 width=54) (actual time=4,101,669.325..4,236,587.778 rows=9,136 loops=1)

  • Buffers: shared hit=3,082,100,884 read=5,750,813, temp written=874,041
4. 230.464 4,236,499.075 ↓ 72.0 17,707 1

Nested Loop (cost=219.29..1,026,580.16 rows=246 width=54) (actual time=4,101,669.299..4,236,499.075 rows=17,707 loops=1)

  • Join Filter: ((mut_dossier.dte_mut >= ub_par.dte_deb) AND (mut_dossier.dte_mut <= ub_par.dte_fin))
  • Buffers: shared hit=3,082,029,986 read=5,750,813, temp written=874,041
5. 345.813 4,233,303.678 ↑ 1.3 329,437 1

Nested Loop (cost=218.85..567,701.56 rows=442,558 width=62) (actual time=4,101,662.852..4,233,303.678 rows=329,437 loops=1)

  • Buffers: shared hit=3,079,600,419 read=5,750,813, temp written=874,041
6. 10,706.806 4,231,969.554 ↑ 1.3 329,437 1

Merge Join (cost=218.42..329,398.00 rows=442,558 width=62) (actual time=4,101,662.815..4,231,969.554 rows=329,437 loops=1)

  • Merge Cond: (ub_par.unite_budgetaire_contrat_id = unite_budgetaire_contrat.unite_budgetaire_contrat_id)
  • Join Filter: ((part_comm_dossier.numero_ub >= ub_par.numero_ub_detail_deb) AND (part_comm_dossier.numero_ub <= ub_par.numero_ub_detail_fin))
  • Rows Removed by Join Filter: 108,629,357
  • Buffers: shared hit=3,078,281,794 read=5,750,813, temp written=874,041
7. 0.228 0.228 ↑ 1.3 468 1

Index Scan using ub_c_in_ub_par_fk on ub_par (cost=0.28..22.52 rows=627 width=20) (actual time=0.019..0.228 rows=468 loops=1)

  • Buffers: shared hit=121
8. 33,754.774 4,221,262.520 ↓ 57.2 108,958,794 1

Materialize (cost=218.15..288,213.33 rows=1,904,226 width=54) (actual time=4,101,656.336..4,221,262.520 rows=108,958,794 loops=1)

  • Buffers: shared hit=3,078,281,673 read=5,750,813, temp written=874,041
9. 12,121.261 4,187,507.746 ↓ 57.2 108,958,794 1

Nested Loop (cost=218.15..283,452.76 rows=1,904,226 width=54) (actual time=4,101,656.330..4,187,507.746 rows=108,958,794 loops=1)

  • Buffers: shared hit=3,078,281,673 read=5,750,813
10. 105.212 4,101,821.763 ↓ 42.0 42 1

Nested Loop Semi Join (cost=218.15..3,830.47 rows=1 width=46) (actual time=4,101,656.284..4,101,821.763 rows=42 loops=1)

  • Join Filter: (unite_budgetaire_contrat.numero_ub = unite_budgetaire_contrat_1.numero_ub)
  • Rows Removed by Join Filter: 1,443,772
  • Buffers: shared hit=3,077,385,477 read=5
11. 0.648 0.648 ↑ 1.0 157 1

Index Scan using p_ub_contrat on unite_budgetaire_contrat (cost=0.28..33.58 rows=164 width=46) (actual time=0.069..0.648 rows=157 loops=1)

  • Filter: ((contrat_id = 4,368) AND ((type_ub)::text = 'CORR'::text))
  • Rows Removed by Filter: 363
  • Buffers: shared hit=259 read=1
12. 74.894 4,101,715.903 ↓ 9,196.0 9,196 157

Materialize (cost=217.87..3,794.43 rows=1 width=4) (actual time=2.319..26,125.579 rows=9,196 loops=157)

  • Buffers: shared hit=3,077,385,218 read=4
13. 6.697 4,101,641.009 ↓ 11,292.0 11,292 1

Nested Loop (cost=217.87..3,794.43 rows=1 width=4) (actual time=364.029..4,101,641.009 rows=11,292 loops=1)

  • Buffers: shared hit=3,077,385,218 read=4
14. 9,420.265 4,101,600.436 ↓ 11,292.0 11,292 1

Nested Loop (cost=217.74..3,793.17 rows=1 width=8) (actual time=363.976..4,101,600.436 rows=11,292 loops=1)

  • Join Filter: ((part_comm_dossier_1.numero_ub >= ub_par_1.numero_ub_detail_deb) AND (part_comm_dossier_1.numero_ub <= ub_par_1.numero_ub_detail_fin))
  • Rows Removed by Join Filter: 1,649,045
  • Buffers: shared hit=3,077,362,633 read=4
15. 2,270.831 4,041,004.581 ↓ 8,529,265.0 17,058,530 1

Nested Loop (cost=217.31..3,791.97 rows=2 width=20) (actual time=3.125..4,041,004.581 rows=17,058,530 loops=1)

  • Buffers: shared hit=3,008,105,311 read=4
16. 11,863.641 3,987,558.160 ↓ 8,529,265.0 17,058,530 1

Nested Loop (cost=216.87..3,787.70 rows=2 width=20) (actual time=3.087..3,987,558.160 rows=17,058,530 loops=1)

  • Join Filter: ((mut_dossier_1.dte_mut >= ub_par_1.dte_deb) AND (mut_dossier_1.dte_mut <= ub_par_1.dte_fin))
  • Rows Removed by Join Filter: 67,850
  • Buffers: shared hit=2,939,772,343 read=4
17. 15,622.590 3,962,550.119 ↓ 1,011,107.7 13,144,400 1

Nested Loop (cost=216.60..3,783.38 rows=13 width=20) (actual time=3.069..3,962,550.119 rows=13,144,400 loops=1)

  • Buffers: shared hit=2,897,942,225 read=2
18. 5,331.977 15,231.049 ↓ 194,705.9 30,958,240 1

Nested Loop (cost=216.17..700.65 rows=159 width=16) (actual time=2.645..15,231.049 rows=30,958,240 loops=1)

  • Buffers: shared hit=1,186,743 read=1
19. 1.293 21.372 ↓ 116.7 350 1

Nested Loop (cost=215.74..687.10 rows=3 width=24) (actual time=2.604..21.372 rows=350 loops=1)

  • Buffers: shared hit=11,463 read=1
20. 1.576 4.679 ↓ 7.6 350 1

Hash Join (cost=215.33..238.94 rows=46 width=20) (actual time=2.496..4.679 rows=350 loops=1)

  • Hash Cond: (unite_budgetaire_contrat_1.contrat_id = contrat_1.contrat_id)
  • Buffers: shared hit=151 read=1
21. 0.643 0.643 ↑ 1.0 520 1

Seq Scan on unite_budgetaire_contrat unite_budgetaire_contrat_1 (cost=0.00..21.20 rows=520 width=12) (actual time=0.004..0.643 rows=520 loops=1)

  • Buffers: shared hit=16
22. 0.000 2.460 ↑ 253.0 2 1

Hash (cost=209.00..209.00 rows=506 width=8) (actual time=2.460..2.460 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=132 read=1
23. 0.743 2.464 ↑ 253.0 2 1

Hash Join (cost=11.78..209.00 rows=506 width=8) (actual time=1.552..2.464 rows=2 loops=1)

  • Hash Cond: (contrat_1.pro_id = bi_prod.pro_id)
  • Buffers: shared hit=132 read=1
24. 1.617 1.617 ↑ 1.0 5,686 1

Seq Scan on contrat contrat_1 (cost=0.00..181.86 rows=5,686 width=8) (actual time=0.007..1.617 rows=5,686 loops=1)

  • Buffers: shared hit=125
25. 0.009 0.104 ↑ 1.0 13 1

Hash (cost=11.62..11.62 rows=13 width=8) (actual time=0.103..0.104 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=7 read=1
26. 0.095 0.095 ↑ 1.0 13 1

Index Scan using biinfra_bi_prod_soc_id_idx on bi_prod (cost=0.14..11.62 rows=13 width=8) (actual time=0.042..0.095 rows=13 loops=1)

  • Index Cond: (soc_id = 6)
  • Buffers: shared hit=7 read=1
27. 5.140 15.400 ↑ 1.0 1 350

Index Only Scan using idx_mut_dossier_no_gen_contrat_contrat_id on mut_contrat (cost=0.42..9.73 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=350)

  • Index Cond: (contrat_id = contrat_1.contrat_id)
  • Filter: (no_gen_contrat = (SubPlan 4))
  • Rows Removed by Filter: 9
  • Heap Fetches: 0
  • Buffers: shared hit=11,312
28.          

SubPlan (for Index Only Scan)

29. 0.000 10.260 ↑ 1.0 1 3,420

Result (cost=0.59..0.60 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3,420)

  • Buffers: shared hit=10,261
30.          

Initplan (for Result)

31. 3.420 10.260 ↑ 1.0 1 3,420

Limit (cost=0.42..0.59 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3,420)

  • Buffers: shared hit=10,261
32. 6.840 6.840 ↑ 15.0 1 3,420

Index Only Scan Backward using olm_perf_i_2020_02097_2 on mut_contrat a_1 (cost=0.42..2.96 rows=15 width=4) (actual time=0.002..0.002 rows=1 loops=3,420)

  • Index Cond: ((contrat_id = mut_contrat.contrat_id) AND (no_gen_contrat IS NOT NULL) AND (etat = 'VALI'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=10,261
33. 9,877.700 9,877.700 ↓ 762.5 88,452 350

Index Scan using c_in_d_fk on dossier dossier_1 (cost=0.42..3.36 rows=116 width=8) (actual time=0.014..28.222 rows=88,452 loops=350)

  • Index Cond: (contrat_id = contrat_1.contrat_id)
  • Buffers: shared hit=1,175,280
34. 1,246,504.320 3,931,696.480 ↓ 0.0 0 30,958,240

Index Scan using olm_perf_i_2020_02097_1 on mut_dossier mut_dossier_1 (cost=0.43..19.38 rows=1 width=12) (actual time=0.125..0.127 rows=0 loops=30,958,240)

  • Index Cond: ((dossier_id = dossier_1.dossier_id) AND ((statut)::text = ANY ('{MIXT,ACTI}'::text[])))
  • Filter: (no_gen_dossier = (SubPlan 6))
  • Rows Removed by Filter: 21
  • Buffers: shared hit=2,896,755,482 read=1
35.          

SubPlan (for Index Scan)

36. 0.000 2,685,192.160 ↑ 1.0 1 671,298,040

Result (cost=0.53..0.54 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=671,298,040)

  • Buffers: shared hit=2,097,921,720
37.          

Initplan (for Result)

38. 0.000 2,685,192.160 ↑ 1.0 1 671,298,040

Limit (cost=0.43..0.53 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=671,298,040)

  • Buffers: shared hit=2,097,921,720
39. 2,685,192.160 2,685,192.160 ↑ 30.0 1 671,298,040

Index Only Scan Backward using olm_perf_i_2020_02097_1 on mut_dossier a_2 (cost=0.43..3.41 rows=30 width=4) (actual time=0.004..0.004 rows=1 loops=671,298,040)

  • Index Cond: ((dossier_id = mut_dossier_1.dossier_id) AND (no_gen_dossier IS NOT NULL) AND (etat = 'VALI'::text))
  • Heap Fetches: 75,452,460
  • Buffers: shared hit=2,097,921,720
40. 13,144.400 13,144.400 ↑ 1.0 1 13,144,400

Index Scan using ub_c_in_ub_par_fk on ub_par ub_par_1 (cost=0.28..0.32 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=13,144,400)

  • Index Cond: (unite_budgetaire_contrat_id = unite_budgetaire_contrat_1.unite_budgetaire_contrat_id)
  • Buffers: shared hit=41,830,118 read=2
41. 51,175.590 51,175.590 ↑ 1.0 1 17,058,530

Index Scan using mu_d_in_d_g_fk on dossier_gen dossier_gen_1 (cost=0.43..2.12 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=17,058,530)

  • Index Cond: (mut_dossier_id = mut_dossier_1.mut_dossier_id)
  • Buffers: shared hit=68,332,968
42. 51,175.590 51,175.590 ↓ 0.0 0 17,058,530

Index Scan using d_g_in_ptc_d_fk on part_comm_dossier part_comm_dossier_1 (cost=0.43..0.58 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=17,058,530)

  • Index Cond: (dossier_gen_id = dossier_gen_1.dossier_gen_id)
  • Filter: (((type_part_comm)::text = 'UB'::text) AND ((type_employeur)::text = 'HOET'::text))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=69,257,322
43. 33.876 33.876 ↑ 1.0 1 11,292

Index Only Scan using bi_soc_pkey on bi_soc (cost=0.13..1.25 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=11,292)

  • Index Cond: (soc_id = 6)
  • Heap Fetches: 11,292
  • Buffers: shared hit=22,585
44. 73,564.722 73,564.722 ↑ 1.0 2,594,257 42

Seq Scan on part_comm_dossier (cost=0.00..253,437.59 rows=2,618,471 width=8) (actual time=0.006..1,751.541 rows=2,594,257 loops=42)

  • Filter: ((type_part_comm)::text = 'UB'::text)
  • Rows Removed by Filter: 5,021,186
  • Buffers: shared hit=896,196 read=5,750,808
45. 988.311 988.311 ↑ 1.0 1 329,437

Index Scan using p_dossier_gen on dossier_gen (cost=0.43..0.54 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=329,437)

  • Index Cond: (dossier_gen_id = part_comm_dossier.dossier_gen_id)
  • Buffers: shared hit=1,318,625
46. 988.311 2,964.933 ↓ 0.0 0 329,437

Index Scan using p_mut_dossier on mut_dossier (cost=0.43..1.02 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=329,437)

  • Index Cond: (mut_dossier_id = dossier_gen.mut_dossier_id)
  • Filter: (no_gen_dossier = (SubPlan 2))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,429,567
47.          

SubPlan (for Index Scan)

48. 0.000 1,976.622 ↑ 1.0 1 329,437

Result (cost=0.53..0.54 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=329,437)

  • Buffers: shared hit=1,110,095
49.          

Initplan (for Result)

50. 329.437 1,976.622 ↑ 1.0 1 329,437

Limit (cost=0.43..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=329,437)

  • Buffers: shared hit=1,110,095
51. 1,647.185 1,647.185 ↑ 30.0 1 329,437

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.005..0.005 rows=1 loops=329,437)

  • Index Cond: ((dossier_id = mut_dossier.dossier_id) AND (no_gen_dossier IS NOT NULL) AND (etat = 'VALI'::text))
  • Heap Fetches: 17,557
  • Buffers: shared hit=1,110,095
52. 70.828 70.828 ↑ 1.0 1 17,707

Index Scan using p_dossier on dossier (cost=0.42..0.65 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=17,707)

  • Index Cond: (dossier_id = mut_dossier.dossier_id)
  • Filter: (contrat_id = 4,368)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=70,898
Planning time : 35.611 ms
Execution time : 4,237,673.921 ms