explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D6XB

Settings
# exclusive inclusive rows x rows loops node
1. 10.061 187.961 ↓ 31.6 1,044 1

GroupAggregate (cost=5,169.60..5,171.42 rows=33 width=222) (actual time=177.360..187.961 rows=1,044 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. 63.518 177.900 ↓ 228.4 7,536 1

Sort (cost=5,169.60..5,169.69 rows=33 width=115) (actual time=177.342..177.900 rows=7,536 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: 1,958kB
3. 4.079 114.382 ↓ 228.4 7,536 1

Hash Join (cost=2,690.88..5,168.77 rows=33 width=115) (actual time=5.727..114.382 rows=7,536 loops=1)

  • Hash Cond: (pre.fk_apporteur = d8_.pk_apporteur)
4. 6.537 110.240 ↓ 228.4 7,536 1

Nested Loop Left Join (cost=2,686.04..5,163.85 rows=33 width=119) (actual time=5.656..110.240 rows=7,536 loops=1)

5. 5.020 96.167 ↓ 228.4 7,536 1

Nested Loop Left Join (cost=2,685.89..5,145.04 rows=33 width=116) (actual time=5.651..96.167 rows=7,536 loops=1)

6. 3.878 76.075 ↓ 228.4 7,536 1

Hash Left Join (cost=2,685.62..5,118.88 rows=33 width=89) (actual time=5.637..76.075 rows=7,536 loops=1)

  • Hash Cond: (pre.fk_situation_professionnelle = d3_.pk_situation_professionnelle)
7. 6.587 71.779 ↓ 228.4 7,536 1

Nested Loop Left Join (cost=2,662.09..5,095.27 rows=33 width=69) (actual time=5.213..71.779 rows=7,536 loops=1)

8. 3.524 50.120 ↓ 228.4 7,536 1

Hash Left Join (cost=2,661.81..5,043.65 rows=33 width=57) (actual time=5.201..50.120 rows=7,536 loops=1)

  • Hash Cond: (pre.fk_regime = d1_.pk_regime)
9. 41.489 46.266 ↓ 228.4 7,536 1

Bitmap Heap Scan on fait_prestation pre (cost=2,639.20..5,020.95 rows=33 width=53) (actual time=4.865..46.266 rows=7,536 loops=1)

  • Recheck Cond: ((fk_client = ANY ('{1766,1768,2473,1774,1776,1779,1830,1789,1790,1949,2375,2376,1941,1829,2434,1844,2436,2445,1848,2489,2490,1858,1863,1864,1868,2427,2439,2377,2482,1884,2462,2440,2378,1899,2379,1909,1915,2380,1919,1923,2381}'::integer[])) AND (fk_contrat = ANY ('{1346,1348,1354,1356,1359,1369,1370,1409,1410,1424,1428,1438,1443,1444,1448,1464,1479,1489,1495,1499,1503,1521,1529,1530,1531,1532,1533,1534,1535,1536,1538,1575,1576,1591,1598,1599,1600,1703,1730,1750,1762,1763,1802,1851,1923,1934,1950,1970,1972,1976,1999,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,930) 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: 17,641
  • Heap Blocks: exact=608
10. 0.027 4.777 ↓ 0.0 0 1

BitmapAnd (cost=2,639.20..2,639.20 rows=1,297 width=0) (actual time=4.777..4.777 rows=0 loops=1)

11. 2.321 2.321 ↑ 4.1 25,860 1

Bitmap Index Scan on fait_prestation_cli (cost=0.00..1,180.11 rows=107,021 width=0) (actual time=2.320..2.321 rows=25,860 loops=1)

  • Index Cond: (fk_client = ANY ('{1766,1768,2473,1774,1776,1779,1830,1789,1790,1949,2375,2376,1941,1829,2434,1844,2436,2445,1848,2489,2490,1858,1863,1864,1868,2427,2439,2377,2482,1884,2462,2440,2378,1899,2379,1909,1915,2380,1919,1923,2381}'::integer[]))
12. 2.429 2.429 ↑ 5.2 25,177 1

Bitmap Index Scan on fait_prestation_con (cost=0.00..1,458.82 rows=131,882 width=0) (actual time=2.428..2.429 rows=25,177 loops=1)

  • Index Cond: (fk_contrat = ANY ('{1346,1348,1354,1356,1359,1369,1370,1409,1410,1424,1428,1438,1443,1444,1448,1464,1479,1489,1495,1499,1503,1521,1529,1530,1531,1532,1533,1534,1535,1536,1538,1575,1576,1591,1598,1599,1600,1703,1730,1750,1762,1763,1802,1851,1923,1934,1950,1970,1972,1976,1999,2001,6969,6970,6972,7328,7331,7781}'::integer[]))
13. 0.183 0.330 ↑ 1.0 694 1

Hash (cost=13.94..13.94 rows=694 width=12) (actual time=0.329..0.330 rows=694 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
14. 0.147 0.147 ↑ 1.0 694 1

Seq Scan on dim_regime d1_ (cost=0.00..13.94 rows=694 width=12) (actual time=0.014..0.147 rows=694 loops=1)

15. 15.072 15.072 ↑ 1.0 1 7,536

Index Scan using dim_client_pkey on dim_client d2_ (cost=0.28..1.56 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=7,536)

  • Index Cond: (pre.fk_client = pk_client)
16. 0.221 0.418 ↑ 1.0 690 1

Hash (cost=14.90..14.90 rows=690 width=28) (actual time=0.418..0.418 rows=690 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
17. 0.197 0.197 ↑ 1.0 690 1

Seq Scan on dim_situation_professionnelle d3_ (cost=0.00..14.90 rows=690 width=28) (actual time=0.008..0.197 rows=690 loops=1)

18. 15.072 15.072 ↑ 1.0 1 7,536

Index Scan using dim_acte_pkey on dim_acte d4_ (cost=0.28..0.79 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=7,536)

  • Index Cond: (pre.fk_acte = pk_acte)
19. 7.536 7.536 ↑ 1.0 1 7,536

Index Scan using dim_reseau_pkey on dim_reseau d5_ (cost=0.15..0.57 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=7,536)

  • Index Cond: (pre.fk_reseau_soin = pk_reseau)
20. 0.025 0.063 ↓ 1.0 114 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
21. 0.038 0.038 ↓ 1.0 114 1

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

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