explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CCPl : Optimization for: plan #UyeE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9.296 694,329.999 ↑ 3.0 11,479 1

Append (cost=276,665.15..202,519,201.58 rows=33,947 width=185) (actual time=185,508.834..694,329.999 rows=11,479 loops=1)

2.          

CTE score

3. 17.250 5,576.975 ↓ 1.5 18,176 1

HashAggregate (cost=56,910.85..57,096.63 rows=12,385 width=16) (actual time=5,571.374..5,576.975 rows=18,176 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. 70.536 5,559.725 ↓ 1.5 18,176 1

Gather (cost=14,138.94..56,787.00 rows=12,385 width=16) (actual time=1,941.029..5,559.725 rows=18,176 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 14.326 5,489.189 ↓ 1.2 6,059 3

Parallel Hash Join (cost=13,138.94..54,548.50 rows=5,160 width=16) (actual time=1,874.487..5,489.189 rows=6,059 loops=3)

  • Hash Cond: (ref_genealogy_1.id_int = filleuls_1.id_int)
6. 10.012 3,642.276 ↓ 1.5 13,795 3

Parallel Hash Join (cost=6,569.47..46,792.09 rows=9,326 width=8) (actual time=39.001..3,642.276 rows=13,795 loops=3)

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

Parallel Seq Scan on ref_genealogy ref_genealogy_1 (cost=0.00..38,124.09 rows=16,855 width=8) (actual time=0.093..3,593.504 rows=13,816 loops=3)

  • Filter: (is_active AND (level = '-1'::integer))
  • Rows Removed by Filter: 151746
8. 2.871 38.760 ↑ 1.8 6,088 3

Parallel Hash (cost=6,429.72..6,429.72 rows=11,180 width=4) (actual time=38.759..38.760 rows=6,088 loops=3)

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

Parallel Index Scan using ref_people_xperf12 on ref_people ref_people_2 (cost=0.42..6,429.72 rows=11,180 width=4) (actual time=0.544..35.889 rows=6,088 loops=3)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_type = 5))
  • Rows Removed by Filter: 7938
10. 12.144 1,832.587 ↑ 1.8 6,088 3

Parallel Hash (cost=6,429.72..6,429.72 rows=11,180 width=20) (actual time=1,832.587..1,832.587 rows=6,088 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1216kB
11. 1,820.443 1,820.443 ↑ 1.8 6,088 3

Parallel Index Scan using ref_people_xperf12 on ref_people filleuls_1 (cost=0.42..6,429.72 rows=11,180 width=20) (actual time=41.594..1,820.443 rows=6,088 loops=3)

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

CTE score_exaequo

13. 456.320 13,848.687 ↓ 2.1 416,872 1

GroupAggregate (cost=177,896.45..181,959.59 rows=203,157 width=20) (actual time=13,308.397..13,848.687 rows=416,872 loops=1)

  • Group Key: a.id_int, ref_mandat.signature_date
14. 419.125 13,392.367 ↓ 2.2 451,448 1

Sort (cost=177,896.45..178,404.34 rows=203,157 width=16) (actual time=13,308.374..13,392.367 rows=451,448 loops=1)

  • Sort Key: a.id_int, ref_mandat.signature_date
  • Sort Method: external merge Disk: 13232kB
15. 78.895 12,973.242 ↓ 2.2 451,448 1

Gather (cost=137,294.54..159,985.89 rows=203,157 width=16) (actual time=12,820.873..12,973.242 rows=451,448 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 52.520 12,894.347 ↓ 1.8 150,483 3

Merge Join (cost=136,294.54..138,670.19 rows=84,649 width=16) (actual time=12,813.657..12,894.347 rows=150,483 loops=3)

  • Merge Cond: (ref_mandat.id_int_agent = a.id_int)
17. 128.374 11,089.330 ↑ 1.3 224,976 3

Sort (cost=128,633.96..129,379.81 rows=298,340 width=16) (actual time=11,072.305..11,089.330 rows=224,976 loops=3)

  • Sort Key: ref_mandat.id_int_agent
  • Sort Method: quicksort Memory: 17216kB
  • Worker 0: Sort Method: quicksort Memory: 16681kB
  • Worker 1: Sort Method: quicksort Memory: 17424kB
18. 10,960.956 10,960.956 ↑ 1.3 233,865 3

Parallel Index Scan using ref_mandat_xperf02 on ref_mandat (cost=0.42..101,505.01 rows=298,340 width=16) (actual time=14.023..10,960.956 rows=233,865 loops=3)

19. 19.055 1,752.497 ↓ 11.7 153,990 3

Sort (cost=7,660.56..7,693.42 rows=13,143 width=4) (actual time=1,741.318..1,752.497 rows=153,990 loops=3)

  • Sort Key: a.id_int
  • Sort Method: quicksort Memory: 802kB
  • Worker 0: Sort Method: quicksort Memory: 802kB
  • Worker 1: Sort Method: quicksort Memory: 802kB
20. 1,733.442 1,733.442 ↑ 1.5 8,900 3

Index Scan using ref_people_xperf12 on ref_people a (cost=0.42..6,761.45 rows=13,143 width=4) (actual time=22.315..1,733.442 rows=8,900 loops=3)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 33179
21. 1.562 185,510.566 ↓ 429.8 2,579 1

Unique (cost=37,608.94..37,609.22 rows=6 width=185) (actual time=185,508.833..185,510.566 rows=2,579 loops=1)

22. 34.005 185,509.004 ↓ 429.8 2,579 1

Sort (cost=37,608.94..37,608.95 rows=6 width=185) (actual time=185,508.830..185,509.004 rows=2,579 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: 782kB
23. 75.900 185,474.999 ↓ 429.8 2,579 1

Nested Loop (cost=6.02..37,608.86 rows=6 width=185) (actual time=19,637.805..185,474.999 rows=2,579 loops=1)

24. 6.379 335.133 ↓ 578.6 5,786 1

Nested Loop (cost=5.59..1,839.82 rows=10 width=80) (actual time=2.396..335.133 rows=5,786 loops=1)

25. 0.191 25.094 ↓ 18.0 36 1

Nested Loop (cost=5.17..1,790.88 rows=2 width=80) (actual time=2.353..25.094 rows=36 loops=1)

26. 5.856 7.273 ↓ 2.0 41 1

Bitmap Heap Scan on top_challenge_list (cost=4.74..25.83 rows=21 width=52) (actual time=2.277..7.273 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
27. 1.417 1.417 ↑ 1.0 62 1

Bitmap Index Scan on top_challenge_list_xperf5 (cost=0.00..4.74 rows=62 width=0) (actual time=1.417..1.417 rows=62 loops=1)

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

Index Scan using ref_people_xperf01 on ref_people (cost=0.42..84.04 rows=1 width=28) (actual time=0.396..0.430 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
29. 303.660 303.660 ↓ 16.1 161 36

Index Scan using ref_genealogy_xperf3 on ref_genealogy (cost=0.42..24.37 rows=10 width=8) (actual time=1.475..8.435 rows=161 loops=36)

  • Index Cond: ((id_int = ref_people.id_int) AND (level >= 0) AND (level <= 5))
  • Filter: is_active
30. 14,447.642 14,447.642 ↓ 0.0 0 5,786

Index Scan using ref_people_xperf01 on ref_people filleuls (cost=0.42..1.84 rows=1 width=45) (actual time=1.128..2.497 rows=0 loops=5,786)

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

SubPlan (forNested Loop)

32. 38.685 18,452.745 ↑ 1.0 1 2,579

Aggregate (cost=371.55..371.56 rows=1 width=8) (actual time=7.155..7.155 rows=1 loops=2,579)

33. 18,414.060 18,414.060 ↓ 0.0 0 2,579

CTE Scan on score (cost=0.00..371.55 rows=1 width=4) (actual time=6.724..7.140 rows=0 loops=2,579)

  • 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: 18176
34. 18.053 152,163.579 ↑ 1.0 1 2,579

Aggregate (cost=5,586.83..5,586.84 rows=1 width=32) (actual time=59.001..59.001 rows=1 loops=2,579)

35. 152,145.526 152,145.526 ↓ 1.4 7 2,579

CTE Scan on score_exaequo (cost=0.00..5,586.82 rows=5 width=8) (actual time=52.690..58.994 rows=7 loops=2,579)

  • 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: 416865
36. 205.080 508,810.137 ↑ 3.8 8,900 1

Hash Join (cost=26.29..202,242,026.94 rows=33,941 width=185) (actual time=58.543..508,810.137 rows=8,900 loops=1)

  • Hash Cond: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
37. 7,148.356 7,148.356 ↑ 1.5 8,900 1

Index Scan using ref_people_xperf12 on ref_people ref_people_1 (cost=0.42..6,761.45 rows=13,143 width=45) (actual time=1.009..7,148.356 rows=8,900 loops=1)

  • Index Cond: (is_active = true)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 33179
38. 0.008 4.001 ↓ 1.3 4 1

Hash (cost=25.82..25.82 rows=3 width=52) (actual time=4.001..4.001 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 2.118 3.993 ↓ 1.3 4 1

Bitmap Heap Scan on top_challenge_list top_challenge_list_1 (cost=4.74..25.82 rows=3 width=52) (actual time=1.893..3.993 rows=4 loops=1)

  • Recheck Cond: ((template_challenge)::text = 'top_sponsors'::text)
  • Filter: ((id_int_manager IS NULL) AND is_active AND ((date_fin_challenge)::date <= '2019-06-30'::date))
  • Rows Removed by Filter: 58
  • Heap Blocks: exact=19
40. 1.875 1.875 ↑ 1.0 62 1

Bitmap Index Scan on top_challenge_list_xperf5 (cost=0.00..4.74 rows=62 width=0) (actual time=1.875..1.875 rows=62 loops=1)

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

SubPlan (forHash Join)

42. 142.400 42,764.500 ↑ 1.0 1 8,900

Aggregate (cost=371.55..371.56 rows=1 width=8) (actual time=4.805..4.805 rows=1 loops=8,900)

43. 42,622.100 42,622.100 ↓ 0.0 0 8,900

CTE Scan on score score_1 (cost=0.00..371.55 rows=1 width=4) (actual time=4.133..4.789 rows=0 loops=8,900)

  • 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: 18176
44. 71.200 458,688.200 ↑ 1.0 1 8,900

Aggregate (cost=5,586.83..5,586.84 rows=1 width=32) (actual time=51.538..51.538 rows=1 loops=8,900)

45. 458,617.000 458,617.000 ↓ 2.2 11 8,900

CTE Scan on score_exaequo score_exaequo_1 (cost=0.00..5,586.82 rows=5 width=8) (actual time=41.771..51.530 rows=11 loops=8,900)

  • 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: 416861
Planning time : 121.732 ms