explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SQiU : AVANT MODIF

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.896 44.144 ↓ 2.6 47 1

GroupAggregate (cost=3,858.21..3,858.71 rows=18 width=69) (actual time=43.140..44.144 rows=47 loops=1)

  • Group Key: d1_.lb_regime, d2_.lb_societe, d4_.lb_famille_college, d5_.pk_contrat
2. 4.817 43.248 ↓ 191.1 3,439 1

Sort (cost=3,858.21..3,858.26 rows=18 width=43) (actual time=43.128..43.248 rows=3,439 loops=1)

  • Sort Key: d1_.lb_regime, d2_.lb_societe, d4_.lb_famille_college, d5_.pk_contrat
  • Sort Method: quicksort Memory: 366kB
3. 0.890 38.431 ↓ 191.1 3,439 1

Hash Join (cost=2,167.48..3,857.84 rows=18 width=43) (actual time=2.520..38.431 rows=3,439 loops=1)

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

Nested Loop Left Join (cost=2,162.64..3,852.95 rows=18 width=47) (actual time=2.447..37.475 rows=3,439 loops=1)

5. 1.346 32.609 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,162.36..3,815.65 rows=18 width=47) (actual time=2.441..32.609 rows=3,439 loops=1)

6. 1.679 27.824 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,162.08..3,796.08 rows=18 width=42) (actual time=2.435..27.824 rows=3,439 loops=1)

7. 1.998 22.706 ↓ 191.1 3,439 1

Nested Loop Left Join (cost=2,161.80..3,762.13 rows=18 width=30) (actual time=2.430..22.706 rows=3,439 loops=1)

8. 14.928 17.269 ↓ 191.1 3,439 1

Bitmap Heap Scan on fait_prestation pre (cost=2,161.53..3,743.66 rows=18 width=26) (actual time=2.421..17.269 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.037 2.341 ↓ 0.0 0 1

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

10. 1.049 1.049 ↑ 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.049..1.049 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.255 1.255 ↑ 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.255..1.255 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.015 0.066 ↓ 1.0 114 1

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

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

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

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