explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g2Lh : APRES MODIF

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.482 45.399 ↓ 13.1 235 1

GroupAggregate (cost=3,858.21..3,859.02 rows=18 width=169) (actual time=43.851..45.399 rows=235 loops=1)

  • Group Key: d1_.lb_regime, d2_.lb_societe, d4_.lb_famille_college, d5_.pk_contrat, pre.nb_age_benefici
2. 8.627 43.917 ↓ 191.1 3,439 1

Sort (cost=3,858.21..3,858.26 rows=18 width=62) (actual time=43.832..43.917 rows=3,439 loops=1)

  • Sort Key: d1_.lb_regime, d2_.lb_societe, d4_.lb_famille_college, d5_.pk_contrat, pre.nb_age_benefici
  • Sort Method: quicksort Memory: 575kB
3. 0.905 35.290 ↓ 191.1 3,439 1

Hash Join (cost=2,167.48..3,857.84 rows=18 width=62) (actual time=2.386..35.290 rows=3,439 loops=1)

  • Hash Cond: (pre.fk_apporteur = d8_.pk_apporteur)
4. 1.025 34.342 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,162.64..3,852.95 rows=18 width=66) (actual time=2.337..34.342 rows=3,439 loops=1)

5. 0.862 29.878 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,162.36..3,815.65 rows=18 width=66) (actual time=2.331..29.878 rows=3,439 loops=1)

6. 1.370 25.577 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,162.08..3,796.08 rows=18 width=61) (actual time=2.326..25.577 rows=3,439 loops=1)

7. 0.987 20.768 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,161.80..3,762.13 rows=18 width=49) (actual time=2.320..20.768 rows=3,439 loops=1)

8. 14.093 16.342 ↓ 191.1 3,439 1

Bitmap Heap Scan on fait_prestation pre (cost=2,161.53..3,743.66 rows=18 width=45) (actual time=2.310..16.342 rows=3,439 loops=1)

  • Recheck Cond: ((fk_client = ANY ('{1766,2473,1774,1776,1789,1790,2375,2376,1941,1829,1844,2436,2445,1848,2489,2490,1858,1863,1864,1868,2427,2439,2482,1884,2462,2440,2378,1899,2379,1909,2380,1919,1923,2381}'::integer[])) AND (fk_contrat = ANY ('{1346,1354,1356,1369,1370,1409,1424,1428,1438,1443,1444,1448,1464,1479,1489,1499,1503,1521,1530,1531,1533,1534,1535,1536,1538,1575,1576,1591,1598,1599,1600,1730,1750,1762,1763,1802,1851,1970,1972,1976,2001,6969,6970,6972,7328,7331,7781}'::integer[])))
  • Filter: ((fk_cal_date_debut_soins >= 20,200,101) AND (fk_cal_date_debut_soins <= 20,201,231) AND (fk_cal_date_paiement <= 20,200,331) AND (fk_assureur = 30) AND (fk_situation_professionnelle = ANY ('{184,652,477,273,394,272,70,350,406,670,576,309,292,509,663,271,156,417,641,475,282,173,390,733,502,42,189,117,161,257,40,700,632,658,587,120,578,695,440,349,187,57,19,160,644,506,357,242,331,599,677,266,366,307,54,305,4,698,181,466,10,631,6,446,220,400,219,241,363,596,661,523,356,13,414,2,640,699,368,128,595,152,428,355,637,651,381,359,597,193,164,730,548,453,709,501,182,18,145,563,143,496,718,623,667,668,294,229,304,645,607,438,633,382,258,483,450,405,170,192,276,513,101,473,646,379,679,115,375,415,108,729,680,408,589,689,522,674,682,567,630,575,401,676,153,507,279,419,716,664,565,551,566,532,457,592,281,687,259,553,519,302,172,409,30,288,622,3,398,249,635,255,56,639,296,620,138,418,407,673,443,484,144,690,598,393,524,312,167,498,277,290,469,683,118,300,608,284,317,662,342,702,488,126,360,463,99,476,629,367,588,206,439,586,32,572,336,275,247,260,423,136,354,139,647,593,479,293,411,425,672,495,154,657,234,316,403,256,332,654,675,628,696,422,549,185,264,380,653,270,612,435,617,351,116,552,426,431,364,491,462,299,169,345,605,180,660,437,236,626,263,135,711,707,665,470,454,500,478,577,311,505,280,121,239,188,461,561,240,717,634,650,291,703,710,447,298,244,602,691,468,697,721,251,204,705,171,456,397,531,427,604,347,283,179,624,546,562,686,297,303,105,402,643,472,233,252,343,31,467,692,581,14,497,155,133,609,416,195,199,369,600,243,335,591,603,383,230,301,254,503,573,432,250,611,619,621,517,129,203,585,346,455,71,267,684,68,713,448,396,701,162,132,582,389,330,237,451,262,337,348,504,399,238,547,518,289,613,268,583,569,124,615,452,693,98,200,490,527,235,601,590,627,119,306,286,376,202,685,358,412,564,678,72,579,510,642,232,77,131,198,37,212,584,720,353,489,515,574,616,706,83,571,516,395,666,694,669,223,352,36,362,221,688,158,594,460,533,499,265,16,207,123,568,492,365,704,157,413,606,649,618,308,183,656,140,248,421,137,208,392,429,122,141,648,570,434,671,580,465,449,520}'::integer[])))
  • Rows Removed by Filter: 19,857
  • Heap Blocks: exact=558
9. 0.019 2.249 ↓ 0.0 0 1

BitmapAnd (cost=2,161.53..2,161.53 rows=871 width=0) (actual time=2.249..2.249 rows=0 loops=1)

10. 1.034 1.034 ↑ 3.7 24,171 1

Bitmap Index Scan on fait_prestation_cli (cost=0.00..978.44 rows=88,749 width=0) (actual time=1.034..1.034 rows=24,171 loops=1)

  • Index Cond: (fk_client = ANY ('{1766,2473,1774,1776,1789,1790,2375,2376,1941,1829,1844,2436,2445,1848,2489,2490,1858,1863,1864,1868,2427,2439,2482,1884,2462,2440,2378,1899,2379,1909,2380,1919,1923,2381}'::integer[]))
11. 1.196 1.196 ↑ 4.6 23,296 1

Bitmap Index Scan on fait_prestation_con (cost=0.00..1,182.83 rows=106,870 width=0) (actual time=1.196..1.196 rows=23,296 loops=1)

  • Index Cond: (fk_contrat = ANY ('{1346,1354,1356,1369,1370,1409,1424,1428,1438,1443,1444,1448,1464,1479,1489,1499,1503,1521,1530,1531,1533,1534,1535,1536,1538,1575,1576,1591,1598,1599,1600,1730,1750,1762,1763,1802,1851,1970,1972,1976,2001,6969,6970,6972,7328,7331,7781}'::integer[]))
12. 3.439 3.439 ↑ 1.0 1 3,439

Index Scan using dim_regime_pkey on dim_regime d1_ (cost=0.28..1.03 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pre.fk_regime = pk_regime)
13. 3.439 3.439 ↑ 1.0 1 3,439

Index Scan using dim_client_pkey on dim_client d2_ (cost=0.28..1.89 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pre.fk_client = pk_client)
14. 3.439 3.439 ↑ 1.0 1 3,439

Index Scan using dim_situation_professionnelle_pkey on dim_situation_professionnelle d4_ (cost=0.28..1.09 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pre.fk_situation_professionnelle = pk_situation_professionnelle)
15. 3.439 3.439 ↑ 1.0 1 3,439

Index Only Scan using dim_contrat_pkey on dim_contrat d5_ (cost=0.28..2.07 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pk_contrat = pre.fk_contrat)
  • Heap Fetches: 3,439
16. 0.018 0.043 ↓ 1.0 114 1

Hash (cost=3.42..3.42 rows=113 width=4) (actual time=0.043..0.043 rows=114 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
17. 0.025 0.025 ↓ 1.0 114 1

Seq Scan on dim_apporteur d8_ (cost=0.00..3.42 rows=113 width=4) (actual time=0.012..0.025 rows=114 loops=1)

  • Filter: ((nb_apporteur)::text <> 'null'::text)
Planning time : 3.142 ms