explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7es

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 51.009 34,634.885 ↓ 1.6 45,377 1

Unique (cost=20,332,181.80..20,333,627.34 rows=27,534 width=510) (actual time=34,525.450..34,634.885 rows=45,377 loops=1)

2. 1,534.147 34,583.876 ↓ 1.6 45,377 1

Sort (cost=20,332,181.80..20,332,250.64 rows=27,534 width=510) (actual time=34,525.446..34,583.876 rows=45,377 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, ((COALESCE((SubPlan 1), '0'::bigint) + COALESCE((SubPlan 2), '0'::bigint))), (0)
  • Sort Method: external merge Disk: 5960kB
3. 25.724 33,049.729 ↓ 1.6 45,377 1

Append (cost=1.27..20,323,844.32 rows=27,534 width=510) (actual time=4.738..33,049.729 rows=45,377 loops=1)

4. 482.162 20,178.826 ↓ 366.0 31,113 1

Nested Loop (cost=1.27..69,723.45 rows=85 width=164) (actual time=4.737..20,178.826 rows=31,113 loops=1)

5. 65.963 225.305 ↓ 396.3 34,872 1

Nested Loop (cost=0.84..6,991.86 rows=88 width=85) (actual time=0.994..225.305 rows=34,872 loops=1)

6. 0.515 17.098 ↓ 10.8 172 1

Nested Loop (cost=0.42..6,787.80 rows=16 width=85) (actual time=0.132..17.098 rows=172 loops=1)

7. 1.008 1.008 ↓ 1.0 175 1

Seq Scan on top_challenge_list (cost=0.00..30.26 rows=173 width=57) (actual time=0.049..1.008 rows=175 loops=1)

  • Filter: (is_active AND ((template_challenge)::text = 'top_sales'::text))
  • Rows Removed by Filter: 326
8. 15.575 15.575 ↑ 1.0 1 175

Index Scan using ref_people_xperf1 on ref_people (cost=0.42..39.05 rows=1 width=28) (actual time=0.066..0.089 rows=1 loops=175)

  • Index Cond: (id_int = top_challenge_list.id_int_manager)
  • Filter: (is_active AND (id_statut <> 2) AND (COALESCE(id_qualification, 1) >= 1) AND (id_type = 5))
  • Rows Removed by Filter: 9
9. 142.244 142.244 ↓ 22.6 203 172

Index Scan using ref_genealogy_xperf5 on ref_genealogy (cost=0.42..12.66 rows=9 width=8) (actual time=0.080..0.827 rows=203 loops=172)

  • Index Cond: (id_int = ref_people.id_int)
  • Filter: (is_active AND (level >= 0) AND (level <= 5))
  • Rows Removed by Filter: 66
10. 4,568.232 4,568.232 ↑ 1.0 1 34,872

Index Scan using ref_people_xperf1 on ref_people filleuls (cost=0.42..1.08 rows=1 width=47) (actual time=0.055..0.131 rows=1 loops=34,872)

  • Index Cond: (id_int = ref_genealogy.id_int_level)
  • Filter: (is_active AND (id_type = ANY ('{5,14}'::integer[])))
  • Rows Removed by Filter: 8
11.          

SubPlan (forNested Loop)

12. 93.339 11,014.002 ↑ 1.0 1 31,113

Aggregate (cost=374.70..374.71 rows=1 width=8) (actual time=0.354..0.354 rows=1 loops=31,113)

13. 10,920.663 10,920.663 ↑ 1.0 1 31,113

Index Scan using ref_transaction_xperf3 on ref_transaction (cost=0.42..374.70 rows=1 width=0) (actual time=0.147..0.351 rows=1 loops=31,113)

  • Index Cond: (id_int_agent_in = filleuls.id_int)
  • Filter: (is_active AND (date_compromis IS NOT NULL) AND (date_validation IS NOT NULL) AND (num_vente IS NOT NULL) AND ((date_validation)::date >= (top_challenge_list.date_deb_challenge)::date) AND ((date_validation)::date <= (top_challenge_list.date_fin_challenge)::date))
  • Rows Removed by Filter: 23
14. 93.339 3,889.125 ↑ 1.0 1 31,113

Aggregate (cost=362.15..362.16 rows=1 width=8) (actual time=0.125..0.125 rows=1 loops=31,113)

15. 3,795.786 3,795.786 ↑ 1.0 1 31,113

Index Scan using ref_transaction_xperf4 on ref_transaction ref_transaction_1 (cost=0.42..362.15 rows=1 width=0) (actual time=0.061..0.122 rows=1 loops=31,113)

  • Index Cond: (id_int_agent_out = filleuls.id_int)
  • Filter: (is_active AND (date_compromis IS NOT NULL) AND (date_validation IS NOT NULL) AND (num_vente IS NOT NULL) AND ((date_validation)::date >= (top_challenge_list.date_deb_challenge)::date) AND ((date_validation)::date <= (top_challenge_list.date_fin_challenge)::date))
  • Rows Removed by Filter: 23
16. 173.028 12,845.179 ↑ 1.9 14,264 1

Nested Loop (cost=1,004.44..20,253,707.86 rows=27,449 width=164) (actual time=10.214..12,845.179 rows=14,264 loops=1)

  • Join Filter: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
  • Rows Removed by Join Filter: 1308
17. 0.000 26.461 ↓ 2.2 7,786 1

Gather (cost=1,000.00..26,283.43 rows=3,611 width=47) (actual time=9.901..26.461 rows=7,786 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 316.686 316.686 ↓ 1.7 2,595 3

Parallel Seq Scan on ref_people ref_people_1 (cost=0.00..24,922.33 rows=1,505 width=47) (actual time=1.007..316.686 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: 107270
19. 7.706 7.786 ↑ 4.0 2 7,786

Materialize (cost=4.44..29.56 rows=8 width=57) (actual time=0.000..0.001 rows=2 loops=7,786)

20. 0.043 0.080 ↑ 4.0 2 1

Bitmap Heap Scan on top_challenge_list top_challenge_list_1 (cost=4.44..29.52 rows=8 width=57) (actual time=0.052..0.080 rows=2 loops=1)

  • Recheck Cond: (id_int_manager IS NULL)
  • Filter: (is_active AND ((template_challenge)::text = 'top_sales'::text))
  • Rows Removed by Filter: 20
  • Heap Blocks: exact=3
21. 0.037 0.037 ↑ 1.0 22 1

Bitmap Index Scan on top_challenge_xperf3 (cost=0.00..4.44 rows=22 width=0) (actual time=0.037..0.037 rows=22 loops=1)

  • Index Cond: (id_int_manager IS NULL)
22.          

SubPlan (forNested Loop)

23. 57.056 8,815.152 ↑ 1.0 1 14,264

Aggregate (cost=374.70..374.71 rows=1 width=8) (actual time=0.617..0.618 rows=1 loops=14,264)

24. 8,758.096 8,758.096 ↓ 2.0 2 14,264

Index Scan using ref_transaction_xperf3 on ref_transaction ref_transaction_2 (cost=0.42..374.70 rows=1 width=0) (actual time=0.145..0.614 rows=2 loops=14,264)

  • Index Cond: (id_int_agent_in = ref_people_1.id_int)
  • Filter: (is_active AND (date_compromis IS NOT NULL) AND (date_validation IS NOT NULL) AND (num_vente IS NOT NULL) AND ((date_validation)::date >= (top_challenge_list_1.date_deb_challenge)::date) AND ((date_validation)::date <= (top_challenge_list_1.date_fin_challenge)::date))
  • Rows Removed by Filter: 65
25. 71.320 3,822.752 ↑ 1.0 1 14,264

Aggregate (cost=362.15..362.16 rows=1 width=8) (actual time=0.268..0.268 rows=1 loops=14,264)

26. 3,751.432 3,751.432 ↓ 2.0 2 14,264

Index Scan using ref_transaction_xperf4 on ref_transaction ref_transaction_3 (cost=0.42..362.15 rows=1 width=0) (actual time=0.123..0.263 rows=2 loops=14,264)

  • Index Cond: (id_int_agent_out = ref_people_1.id_int)
  • Filter: (is_active AND (date_compromis IS NOT NULL) AND (date_validation IS NOT NULL) AND (num_vente IS NOT NULL) AND ((date_validation)::date >= (top_challenge_list_1.date_deb_challenge)::date) AND ((date_validation)::date <= (top_challenge_list_1.date_fin_challenge)::date))
  • Rows Removed by Filter: 65
Planning time : 5.109 ms
Execution time : 34,639.782 ms