explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n7Og : AVEC JOINTURE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.211 68.082 ↓ 21.4 579 1

GroupAggregate (cost=5,206.20..5,207.69 rows=27 width=222) (actual time=65.785..68.082 rows=579 loops=1)

  • Group Key: d1_.lb_regime, d2_.lb_societe, d3_.lb_famille_college, pre.fk_contrat, pre.nb_age_benefici, d3_.lb_libelle_college, d4_.lb_acte, d4_.lb_famille, d5_.lb_reseau
2. 16.311 65.871 ↓ 127.4 3,439 1

Sort (cost=5,206.20..5,206.27 rows=27 width=115) (actual time=65.770..65.871 rows=3,439 loops=1)

  • Sort Key: d1_.lb_regime, d2_.lb_societe, d3_.lb_famille_college, pre.fk_contrat, pre.nb_age_benefici, d3_.lb_libelle_college, d4_.lb_acte, d4_.lb_famille, d5_.lb_reseau
  • Sort Method: quicksort Memory: 908kB
3. 1.453 49.560 ↓ 127.4 3,439 1

Hash Join (cost=2,719.61..5,205.56 rows=27 width=115) (actual time=3.627..49.560 rows=3,439 loops=1)

  • Hash Cond: (pre.fk_apporteur = d8_.pk_apporteur)
4. 0.804 48.037 ↓ 127.4 3,439 1

Nested Loop Left Join (cost=2,714.77..5,200.65 rows=27 width=119) (actual time=3.549..48.037 rows=3,439 loops=1)

5. 2.540 43.794 ↓ 127.4 3,439 1

Nested Loop Left Join (cost=2,714.62..5,182.86 rows=27 width=116) (actual time=3.542..43.794 rows=3,439 loops=1)

6. 2.239 37.815 ↓ 127.4 3,439 1

Nested Loop Left Join (cost=2,714.34..5,158.46 rows=27 width=89) (actual time=3.536..37.815 rows=3,439 loops=1)

7. 2.327 32.137 ↓ 127.4 3,439 1

Nested Loop Left Join (cost=2,714.07..5,136.26 rows=27 width=69) (actual time=3.529..32.137 rows=3,439 loops=1)

8. 3.046 26.371 ↓ 127.4 3,439 1

Nested Loop Left Join (cost=2,713.79..5,089.73 rows=27 width=57) (actual time=3.520..26.371 rows=3,439 loops=1)

9. 16.468 19.886 ↓ 127.4 3,439 1

Bitmap Heap Scan on fait_prestation pre (cost=2,713.51..5,068.63 rows=27 width=53) (actual time=3.505..19.886 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 ('{2021,1836,1530,1973,1972,2016,1599,1802,1851,1346,1762,1883,1850,1428,1739,1369,1489,1533,1521,1764,1576,1923,1443,1575,1693,1974,1746,1536,1765,1464,7781,1970,1934,1409,1598,7328,1356,1831,1896,1479,1438,1646,1750,1538,2001,1534,7331,1354,6972,1905,1531,1424,1703,6969,1503,1730,1838,1591,1763,6970,1950,1370,1499,1535,1448,1600,1444,1953,1976}'::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: 20,732
  • Heap Blocks: exact=601
10. 0.026 3.418 ↓ 0.0 0 1

BitmapAnd (cost=2,713.51..2,713.51 rows=1,279 width=0) (actual time=3.418..3.418 rows=0 loops=1)

11. 1.690 1.690 ↑ 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.690..1.690 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[]))
12. 1.702 1.702 ↑ 6.5 24,171 1

Bitmap Index Scan on fait_prestation_con (cost=0.00..1,734.81 rows=156,894 width=0) (actual time=1.701..1.702 rows=24,171 loops=1)

  • Index Cond: (fk_contrat = ANY ('{2021,1836,1530,1973,1972,2016,1599,1802,1851,1346,1762,1883,1850,1428,1739,1369,1489,1533,1521,1764,1576,1923,1443,1575,1693,1974,1746,1536,1765,1464,7781,1970,1934,1409,1598,7328,1356,1831,1896,1479,1438,1646,1750,1538,2001,1534,7331,1354,6972,1905,1531,1424,1703,6969,1503,1730,1838,1591,1763,6970,1950,1370,1499,1535,1448,1600,1444,1953,1976}'::integer[]))
13. 3.439 3.439 ↑ 1.0 1 3,439

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

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

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

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

Index Scan using dim_situation_professionnelle_pkey on dim_situation_professionnelle d3_ (cost=0.28..0.82 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=3,439)

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

Index Scan using dim_acte_pkey on dim_acte d4_ (cost=0.28..0.90 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pre.fk_acte = pk_acte)
17. 3.439 3.439 ↑ 1.0 1 3,439

Index Scan using dim_reseau_pkey on dim_reseau d5_ (cost=0.15..0.66 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=3,439)

  • Index Cond: (pre.fk_reseau_soin = pk_reseau)
18. 0.029 0.070 ↓ 1.0 114 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
19. 0.041 0.041 ↓ 1.0 114 1

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

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