explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UyeE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.121 785,747.371 ↑ 1.9 11,473 1

Append (cost=243,056.16..124,432,280.24 rows=22,338 width=185) (actual time=204,128.369..785,747.371 rows=11,473 loops=1)

2.          

CTE score

3. 26.331 4,036.900 ↓ 1.6 18,159 1

HashAggregate (cost=53,967.70..54,141.75 rows=11,603 width=16) (actual time=4,028.463..4,036.900 rows=18,159 loops=1)

  • Group Key: filleuls_1.id_int, ref_people_2.id_int, ((filleuls_1.date_first_activation)::date), ((filleuls_1.date_deactivation_real)::date)
4. 9.242 4,010.569 ↓ 1.6 18,159 1

Gather (cost=14,011.22..53,851.67 rows=11,603 width=16) (actual time=2,282.914..4,010.569 rows=18,159 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 16.648 4,001.327 ↓ 1.3 6,053 3

Parallel Hash Join (cost=13,011.22..51,691.37 rows=4,835 width=16) (actual time=2,260.335..4,001.327 rows=6,053 loops=3)

  • Hash Cond: (ref_genealogy_1.id_int = filleuls_1.id_int)
6. 14.164 3,888.528 ↓ 1.5 13,787 3

Parallel Hash Join (cost=6,505.61..44,975.53 rows=9,007 width=8) (actual time=2,163.676..3,888.528 rows=13,787 loops=3)

  • Hash Cond: (ref_genealogy_1.id_int_level = ref_people_2.id_int)
7. 3,824.902 3,824.902 ↑ 1.2 13,808 3

Parallel Seq Scan on ref_genealogy ref_genealogy_1 (cost=0.00..38,123.29 rows=16,780 width=8) (actual time=2,114.035..3,824.902 rows=13,808 loops=3)

  • Filter: (is_active AND (level = '-1'::integer))
  • Rows Removed by Filter: 151651
8. 3.616 49.462 ↑ 1.8 6,083 3

Parallel Hash (cost=6,369.53..6,369.53 rows=10,886 width=4) (actual time=49.462..49.462 rows=6,083 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1024kB
9. 45.846 45.846 ↑ 1.8 6,083 3

Parallel Index Scan using ref_people_xperf12 on ref_people ref_people_2 (cost=0.42..6,369.53 rows=10,886 width=4) (actual time=0.039..45.846 rows=6,083 loops=3)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_type = 5))
  • Rows Removed by Filter: 7936
10. 4.592 96.151 ↑ 1.8 6,083 3

Parallel Hash (cost=6,369.53..6,369.53 rows=10,886 width=20) (actual time=96.151..96.151 rows=6,083 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1248kB
11. 91.559 91.559 ↑ 1.8 6,083 3

Parallel Index Scan using ref_people_xperf12 on ref_people filleuls_1 (cost=0.42..6,369.53 rows=10,886 width=20) (actual time=0.134..91.559 rows=6,083 loops=3)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_type = 5))
  • Rows Removed by Filter: 7936
12.          

CTE score_exaequo

13. 699.466 25,335.596 ↓ 2.2 416,500 1

GroupAggregate (cost=149,814.74..153,605.70 rows=189,548 width=20) (actual time=24,509.438..25,335.596 rows=416,500 loops=1)

  • Group Key: a.id_int, ref_mandat.signature_date
14. 686.307 24,636.130 ↓ 2.4 451,020 1

Sort (cost=149,814.74..150,288.61 rows=189,548 width=16) (actual time=24,509.415..24,636.130 rows=451,020 loops=1)

  • Sort Key: a.id_int, ref_mandat.signature_date
  • Sort Method: external merge Disk: 13216kB
15. 743.405 23,949.823 ↓ 2.4 451,020 1

Hash Join (cost=6,850.75..133,198.77 rows=189,548 width=16) (actual time=98.400..23,949.823 rows=451,020 loops=1)

  • Hash Cond: (ref_mandat.id_int_agent = a.id_int)
16. 23,111.547 23,111.547 ↓ 1.0 701,085 1

Index Scan using ref_mandat_xperf02 on ref_mandat (cost=0.42..105,574.22 rows=686,500 width=16) (actual time=3.417..23,111.547 rows=701,085 loops=1)

17. 3.370 94.871 ↑ 1.4 8,896 1

Hash (cost=6,689.98..6,689.98 rows=12,827 width=4) (actual time=94.871..94.871 rows=8,896 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 441kB
18. 91.501 91.501 ↑ 1.4 8,896 1

Index Scan using ref_people_xperf12 on ref_people a (cost=0.42..6,689.98 rows=12,827 width=4) (actual time=0.032..91.501 rows=8,896 loops=1)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 33159
19. 1.586 204,130.141 ↓ 429.5 2,577 1

Unique (cost=35,308.71..35,309.00 rows=6 width=185) (actual time=204,128.368..204,130.141 rows=2,577 loops=1)

20. 35.480 204,128.555 ↓ 429.5 2,577 1

Sort (cost=35,308.71..35,308.73 rows=6 width=185) (actual time=204,128.364..204,128.555 rows=2,577 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, ((SubPlan 3)), ((SubPlan 4))
  • Sort Method: quicksort Memory: 781kB
21. 80.738 204,093.075 ↓ 429.5 2,577 1

Nested Loop (cost=6.04..35,308.63 rows=6 width=185) (actual time=29,666.075..204,093.075 rows=2,577 loops=1)

22. 6.332 209.392 ↓ 525.8 5,784 1

Nested Loop (cost=5.61..1,924.06 rows=11 width=80) (actual time=0.153..209.392 rows=5,784 loops=1)

23. 0.261 56.792 ↓ 18.0 36 1

Nested Loop (cost=5.19..1,874.04 rows=2 width=80) (actual time=0.115..56.792 rows=36 loops=1)

24. 0.440 0.484 ↓ 1.9 41 1

Bitmap Heap Scan on top_challenge_list (cost=4.77..25.90 rows=22 width=52) (actual time=0.063..0.484 rows=41 loops=1)

  • Recheck Cond: ((template_challenge)::text = 'top_sponsors'::text)
  • Filter: (is_active AND ((date_fin_challenge)::date <= '2019-06-30'::date))
  • Rows Removed by Filter: 21
  • Heap Blocks: exact=19
25. 0.044 0.044 ↑ 1.0 62 1

Bitmap Index Scan on top_challenge_list_xperf5 (cost=0.00..4.76 rows=65 width=0) (actual time=0.044..0.044 rows=62 loops=1)

  • Index Cond: ((template_challenge)::text = 'top_sponsors'::text)
26. 56.047 56.047 ↑ 1.0 1 41

Index Scan using ref_people_xperf01 on ref_people (cost=0.42..84.00 rows=1 width=28) (actual time=1.332..1.367 rows=1 loops=41)

  • 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: 18
27. 146.268 146.268 ↓ 14.6 161 36

Index Scan using ref_genealogy_xperf3 on ref_genealogy (cost=0.42..24.90 rows=11 width=8) (actual time=1.883..4.063 rows=161 loops=36)

  • Index Cond: ((id_int = ref_people.id_int) AND (level >= 0) AND (level <= 5))
  • Filter: is_active
28. 13,494.072 13,494.072 ↓ 0.0 0 5,784

Index Scan using ref_people_xperf01 on ref_people filleuls (cost=0.42..1.83 rows=1 width=45) (actual time=0.993..2.333 rows=0 loops=5,784)

  • Index Cond: (id_int = ref_genealogy.id_int_level)
  • Filter: (is_active AND (id_type = 5))
  • Rows Removed by Filter: 16
29.          

SubPlan (forNested Loop)

30. 43.809 17,783.877 ↑ 1.0 1 2,577

Aggregate (cost=348.09..348.10 rows=1 width=8) (actual time=6.901..6.901 rows=1 loops=2,577)

31. 17,740.068 17,740.068 ↓ 0.0 0 2,577

CTE Scan on score (cost=0.00..348.09 rows=1 width=4) (actual time=6.446..6.884 rows=0 loops=2,577)

  • Filter: ((date_activation >= top_challenge_list.date_deb_challenge) AND (date_activation <= top_challenge_list.date_fin_challenge) AND ((date_deactivation_real IS NULL) OR (date_deactivation_real > top_challenge_list.date_fin_challenge)) AND (agent = filleuls.id_int))
  • Rows Removed by Filter: 18159
32. 20.616 172,524.996 ↑ 1.0 1 2,577

Aggregate (cost=5,212.59..5,212.60 rows=1 width=32) (actual time=66.948..66.948 rows=1 loops=2,577)

33. 172,504.380 172,504.380 ↓ 1.4 7 2,577

CTE Scan on score_exaequo (cost=0.00..5,212.57 rows=5 width=8) (actual time=60.177..66.940 rows=7 loops=2,577)

  • Filter: ((date_activation >= top_challenge_list.date_deb_challenge) AND (date_activation <= top_challenge_list.date_fin_challenge) AND (agent = filleuls.id_int))
  • Rows Removed by Filter: 416493
34. 252.134 581,609.109 ↑ 2.5 8,896 1

Hash Join (cost=24.33..124,188,888.73 rows=22,332 width=185) (actual time=54.005..581,609.109 rows=8,896 loops=1)

  • Hash Cond: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
35. 34,902.281 34,902.281 ↑ 1.4 8,896 1

Index Scan using ref_people_xperf12 on ref_people ref_people_1 (cost=0.42..6,689.98 rows=12,827 width=45) (actual time=0.040..34,902.281 rows=8,896 loops=1)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 33159
36. 0.006 0.102 ↓ 2.0 4 1

Hash (cost=23.88..23.88 rows=2 width=52) (actual time=0.102..0.102 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.025 0.096 ↓ 2.0 4 1

Bitmap Heap Scan on top_challenge_list top_challenge_list_1 (cost=9.64..23.88 rows=2 width=52) (actual time=0.084..0.096 rows=4 loops=1)

  • Recheck Cond: ((id_int_manager IS NULL) AND ((template_challenge)::text = 'top_sponsors'::text))
  • Filter: (is_active AND ((date_fin_challenge)::date <= '2019-06-30'::date))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=4
38. 0.004 0.071 ↓ 0.0 0 1

BitmapAnd (cost=9.64..9.64 rows=6 width=0) (actual time=0.071..0.071 rows=0 loops=1)

39. 0.018 0.018 ↓ 1.4 64 1

Bitmap Index Scan on top_challenge_list_xperf02 (cost=0.00..4.62 rows=47 width=0) (actual time=0.018..0.018 rows=64 loops=1)

  • Index Cond: (id_int_manager IS NULL)
40. 0.049 0.049 ↑ 1.0 62 1

Bitmap Index Scan on top_challenge_list_xperf5 (cost=0.00..4.76 rows=65 width=0) (actual time=0.049..0.049 rows=62 loops=1)

  • Index Cond: ((template_challenge)::text = 'top_sponsors'::text)
41.          

SubPlan (forHash Join)

42. 142.336 46,481.600 ↑ 1.0 1 8,896

Aggregate (cost=348.09..348.10 rows=1 width=8) (actual time=5.225..5.225 rows=1 loops=8,896)

43. 46,339.264 46,339.264 ↓ 0.0 0 8,896

CTE Scan on score score_1 (cost=0.00..348.09 rows=1 width=4) (actual time=4.504..5.209 rows=0 loops=8,896)

  • Filter: ((date_activation >= top_challenge_list_1.date_deb_challenge) AND (date_activation <= top_challenge_list_1.date_fin_challenge) AND ((date_deactivation_real IS NULL) OR (date_deactivation_real > top_challenge_list_1.date_fin_challenge)) AND (agent = ref_people_1.id_int))
  • Rows Removed by Filter: 18159
44. 88.960 499,972.992 ↑ 1.0 1 8,896

Aggregate (cost=5,212.59..5,212.60 rows=1 width=32) (actual time=56.202..56.202 rows=1 loops=8,896)

45. 499,884.032 499,884.032 ↓ 2.2 11 8,896

CTE Scan on score_exaequo score_exaequo_1 (cost=0.00..5,212.57 rows=5 width=8) (actual time=45.641..56.192 rows=11 loops=8,896)

  • Filter: ((date_activation >= top_challenge_list_1.date_deb_challenge) AND (date_activation <= top_challenge_list_1.date_fin_challenge) AND (agent = ref_people_1.id_int))
  • Rows Removed by Filter: 416489
Planning time : 7.806 ms