explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FP2Ns

Settings
# exclusive inclusive rows x rows loops node
1. 22.852 17,991.803 ↓ 1.7 33,279 1

Unique (cost=289,004.32..290,047.07 rows=19,862 width=510) (actual time=17,949.554..17,991.803 rows=33,279 loops=1)

2.          

CTE score

3. 0.000 13,268.649 ↓ 10.1 97,538 1

Gather (cost=1,000.00..244,540.15 rows=9,642 width=18) (actual time=1.784..13,268.649 rows=97,538 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 13,379.873 13,379.873 ↓ 8.1 32,513 3

Parallel Seq Scan on ref_mandat (cost=0.00..242,575.95 rows=4,018 width=18) (actual time=0.683..13,379.873 rows=32,513 loops=3)

  • Filter: (is_active AND ((COALESCE(validation_status, ''::character varying))::text <> ALL ('{invalidation_mandat,annulation_mandat}'::text[])) AND (upper((type)::text) = ANY ('{"SUCCÈS / SEMI EXCLU",EXCLUSIF,SUCCÈS}'::text[])))
  • Rows Removed by Filter: 1591508
5. 159.557 17,968.951 ↓ 1.7 33,279 1

Sort (cost=44,464.17..44,513.82 rows=19,862 width=510) (actual time=17,949.553..17,968.951 rows=33,279 loops=1)

  • Sort Key: ref_people.id_filiale, top_challenge_list.template_challenge, top_challenge_list.id_challenge, ref_people.id_int, ref_people.id_filiale, ref_people.id_ext, ((((COALESCE(ref_people.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people.name, ''::character varying))::text)), ((top_challenge_list.date_deb_challenge)::date), ((top_challenge_list.date_fin_challenge)::date), top_challenge_list.id_status, top_challenge_list.lib_status, ((top_challenge_list.date_crea)::date), filleuls.id_int, filleuls.id_filiale, filleuls.id_ext, ((((COALESCE(filleuls.firstname, ''::character varying))::text || ' '::text) || (COALESCE(filleuls.name, ''::character varying))::text)), filleuls.id_qualification, filleuls.code_qualification, (count(DISTINCT score.num_mandat)), (0)
  • Sort Method: external merge Disk: 5304kB
6. 2.228 17,809.394 ↓ 1.7 33,279 1

Append (cost=5,468.57..38,496.25 rows=19,862 width=510) (actual time=16,359.864..17,809.394 rows=33,279 loops=1)

7. 46.816 16,480.810 ↓ 422.6 19,015 1

GroupAggregate (cost=5,468.57..5,472.05 rows=45 width=164) (actual time=16,359.864..16,480.810 rows=19,015 loops=1)

  • Group Key: ref_people.id_filiale, top_challenge_list.template_challenge, top_challenge_list.id_challenge, ref_people.id_int, ref_people.id_filiale, ref_people.id_ext, ((((COALESCE(ref_people.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people.name, ''::character varying))::text)), ((top_challenge_list.date_deb_challenge)::date), ((top_challenge_list.date_fin_challenge)::date), top_challenge_list.id_status, top_challenge_list.lib_status, ((top_challenge_list.date_crea)::date), filleuls.id_int, filleuls.id_filiale, filleuls.id_ext, ((((COALESCE(filleuls.firstname, ''::character varying))::text || ' '::text) || (COALESCE(filleuls.name, ''::character varying))::text)), filleuls.id_qualification, filleuls.code_qualification
8. 477.025 16,433.994 ↓ 665.7 29,955 1

Sort (cost=5,468.57..5,468.68 rows=45 width=270) (actual time=16,359.837..16,433.994 rows=29,955 loops=1)

  • Sort Key: ref_people.id_filiale, top_challenge_list.id_challenge, ref_people.id_int, ref_people.id_ext, ((((COALESCE(ref_people.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people.name, ''::character varying))::text)), ((top_challenge_list.date_deb_challenge)::date), ((top_challenge_list.date_fin_challenge)::date), top_challenge_list.id_status, top_challenge_list.lib_status, ((top_challenge_list.date_crea)::date), filleuls.id_int, filleuls.id_filiale, filleuls.id_ext, ((((COALESCE(filleuls.firstname, ''::character varying))::text || ' '::text) || (COALESCE(filleuls.name, ''::character varying))::text)), filleuls.id_qualification, filleuls.code_qualification
  • Sort Method: external merge Disk: 4456kB
9. 168.082 15,956.969 ↓ 665.7 29,955 1

Hash Right Join (cost=5,237.25..5,467.33 rows=45 width=270) (actual time=2,450.038..15,956.969 rows=29,955 loops=1)

  • Hash Cond: (score.id_int_agent = filleuls.id_int)
  • Join Filter: (((score.registered_date)::date >= (top_challenge_list.date_deb_challenge)::date) AND ((score.registered_date)::date <= (top_challenge_list.date_fin_challenge)::date))
  • Rows Removed by Join Filter: 88316
10. 13,340.668 13,340.668 ↓ 10.1 97,538 1

CTE Scan on score (cost=0.00..192.84 rows=9,642 width=130) (actual time=1.789..13,340.668 rows=97,538 loops=1)

11. 21.452 2,448.219 ↓ 422.6 19,015 1

Hash (cost=5,236.68..5,236.68 rows=45 width=128) (actual time=2,448.219..2,448.219 rows=19,015 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3539kB
12. 10.545 2,426.767 ↓ 422.6 19,015 1

Nested Loop (cost=1.27..5,236.68 rows=45 width=128) (actual time=1.715..2,426.767 rows=19,015 loops=1)

13. 5.727 94.643 ↓ 461.6 21,697 1

Nested Loop (cost=0.84..5,186.38 rows=47 width=85) (actual time=1.698..94.643 rows=21,697 loops=1)

14. 0.228 6.332 ↓ 15.5 124 1

Nested Loop (cost=0.42..5,039.58 rows=8 width=85) (actual time=0.726..6.332 rows=124 loops=1)

15. 0.600 0.600 ↓ 1.0 128 1

Seq Scan on top_challenge_list (cost=0.00..30.26 rows=127 width=57) (actual time=0.032..0.600 rows=128 loops=1)

  • Filter: (is_active AND ((template_challenge)::text = 'top_exclusive_mandates_and_successes'::text))
  • Rows Removed by Filter: 373
16. 5.504 5.504 ↑ 1.0 1 128

Index Scan using ref_people_xperf1 on ref_people (cost=0.42..39.43 rows=1 width=28) (actual time=0.035..0.043 rows=1 loops=128)

  • Index Cond: (id_int = top_challenge_list.id_int_manager)
  • Filter: (is_active AND (id_statut <> 2) AND (id_statut <> 2) AND (COALESCE(id_qualification, 1) >= 1) AND (id_type = 5))
  • Rows Removed by Filter: 9
17. 82.584 82.584 ↓ 19.4 175 124

Index Scan using ref_genealogy_xperf5 on ref_genealogy (cost=0.42..18.26 rows=9 width=8) (actual time=0.328..0.666 rows=175 loops=124)

  • Index Cond: (id_int = ref_people.id_int)
  • Filter: (is_active AND (level >= 0) AND (level <= 5))
  • Rows Removed by Filter: 52
18. 2,321.579 2,321.579 ↑ 1.0 1 21,697

Index Scan using ref_people_xperf1 on ref_people filleuls (cost=0.42..1.06 rows=1 width=47) (actual time=0.041..0.107 rows=1 loops=21,697)

  • Index Cond: (id_int = ref_genealogy.id_int_level)
  • Filter: (is_active AND (id_type = ANY ('{5,14}'::integer[])))
  • Rows Removed by Filter: 8
19. 78.143 1,326.356 ↑ 1.4 14,264 1

GroupAggregate (cost=31,190.45..32,726.26 rows=19,817 width=164) (actual time=1,213.513..1,326.356 rows=14,264 loops=1)

  • Group Key: ref_people_1.id_filiale, top_challenge_list_1.template_challenge, top_challenge_list_1.id_challenge, ref_people_1.id_int, ref_people_1.id_filiale, ref_people_1.id_ext, ((((COALESCE(ref_people_1.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people_1.name, ''::character varying))::text)), ((top_challenge_list_1.date_deb_challenge)::date), ((top_challenge_list_1.date_fin_challenge)::date), top_challenge_list_1.id_status, top_challenge_list_1.lib_status, ((top_challenge_list_1.date_crea)::date), ref_people_1.id_int, ref_people_1.id_filiale, ref_people_1.id_ext, ((((COALESCE(ref_people_1.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people_1.name, ''::character varying))::text)), ref_people_1.id_qualification, ref_people_1.code_qualification
20. 186.221 1,248.213 ↓ 1.8 35,497 1

Sort (cost=31,190.45..31,239.99 rows=19,817 width=270) (actual time=1,213.489..1,248.213 rows=35,497 loops=1)

  • Sort Key: ref_people_1.id_filiale, top_challenge_list_1.id_challenge, ref_people_1.id_int, ref_people_1.id_ext, ((((COALESCE(ref_people_1.firstname, ''::character varying))::text || ' '::text) || (COALESCE(ref_people_1.name, ''::character varying))::text)), ((top_challenge_list_1.date_deb_challenge)::date), ((top_challenge_list_1.date_fin_challenge)::date), top_challenge_list_1.id_status, top_challenge_list_1.lib_status, ((top_challenge_list_1.date_crea)::date), ref_people_1.id_qualification, ref_people_1.code_qualification
  • Sort Method: external merge Disk: 5472kB
21. 176.047 1,061.992 ↓ 1.8 35,497 1

Hash Right Join (cost=25,927.96..27,266.56 rows=19,817 width=270) (actual time=868.220..1,061.992 rows=35,497 loops=1)

  • Hash Cond: (score_1.id_int_agent = ref_people_1.id_int)
  • Join Filter: (((score_1.registered_date)::date >= (top_challenge_list_1.date_deb_challenge)::date) AND ((score_1.registered_date)::date <= (top_challenge_list_1.date_fin_challenge)::date))
  • Rows Removed by Join Filter: 137355
22. 17.948 17.948 ↓ 10.1 97,538 1

CTE Scan on score score_1 (cost=0.00..192.84 rows=9,642 width=130) (actual time=0.063..17.948 rows=97,538 loops=1)

23. 10.454 867.997 ↑ 1.4 14,264 1

Hash (cost=25,680.25..25,680.25 rows=19,817 width=100) (actual time=867.997..867.997 rows=14,264 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2335kB
24. 10.304 857.543 ↑ 1.4 14,264 1

Nested Loop (cost=1,000.00..25,680.25 rows=19,817 width=100) (actual time=661.518..857.543 rows=14,264 loops=1)

  • Join Filter: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
  • Rows Removed by Join Filter: 1308
25. 47.508 847.239 ↓ 2.2 7,786 1

Gather (cost=1,000.00..25,337.13 rows=3,476 width=47) (actual time=661.197..847.239 rows=7,786 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 799.731 799.731 ↓ 1.8 2,595 3

Parallel Seq Scan on ref_people ref_people_1 (cost=0.00..23,989.53 rows=1,448 width=47) (actual time=602.541..799.731 rows=2,595 loops=3)

  • Filter: (is_active AND (id_statut <> 2) AND (COALESCE(id_qualification, 1) >= 1) AND (id_type = 5))
  • Rows Removed by Filter: 106503
27. 0.000 0.000 ↑ 3.0 2 7,786

Materialize (cost=0.00..30.29 rows=6 width=57) (actual time=0.000..0.000 rows=2 loops=7,786)

28. 0.301 0.301 ↑ 3.0 2 1

Seq Scan on top_challenge_list top_challenge_list_1 (cost=0.00..30.26 rows=6 width=57) (actual time=0.031..0.301 rows=2 loops=1)

  • Filter: ((id_int_manager IS NULL) AND is_active AND ((template_challenge)::text = 'top_exclusive_mandates_and_successes'::text))
  • Rows Removed by Filter: 499
Planning time : 6.096 ms
Execution time : 18,001.563 ms