explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4H5U : Optimization for: plan #7es

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=186,346,830.44..186,347,854.14 rows=19,499 width=510) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=186,346,830.44..186,346,879.19 rows=19,499 width=510) (actual rows= loops=)

  • 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)
3. 0.000 0.000 ↓ 0.0

Append (cost=1.26..186,340,975.03 rows=19,499 width=510) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..992,271.62 rows=103 width=162) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..7,969.54 rows=114 width=83) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7,461.95 rows=37 width=83) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on top_challenge_list (cost=0.00..84.72 rows=335 width=55) (actual rows= loops=)

  • Filter: (is_active AND ((template_challenge)::text = 'top_sales'::text))
8. 0.000 0.000 ↓ 0.0

Index Scan using ref_people_xperf01 on ref_people (cost=0.42..22.01 rows=1 width=28) (actual rows= loops=)

  • 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))
9. 0.000 0.000 ↓ 0.0

Index Scan using ref_genealogy_xperf3 on ref_genealogy (cost=0.42..13.67 rows=5 width=8) (actual rows= loops=)

  • Index Cond: ((id_int = ref_people.id_int) AND (level >= 0) AND (level <= 5))
  • Filter: is_active
10. 0.000 0.000 ↓ 0.0

Index Scan using ref_people_xperf01 on ref_people filleuls (cost=0.42..0.99 rows=1 width=47) (actual rows= loops=)

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

SubPlan (forNested Loop)

12. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,825.16..4,825.17 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction (cost=29.73..4,825.15 rows=1 width=0) (actual rows= loops=)

  • Recheck 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))
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf03 (cost=0.00..29.73 rows=1,239 width=0) (actual rows= loops=)

  • Index Cond: (id_int_agent_in = filleuls.id_int)
15. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,730.03..4,730.04 rows=1 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_1 (cost=29.54..4,730.02 rows=1 width=0) (actual rows= loops=)

  • Recheck 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))
17. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf04 (cost=0.00..29.54 rows=1,214 width=0) (actual rows= loops=)

  • Index Cond: (id_int_agent_out = filleuls.id_int)
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=795.51..185,348,410.93 rows=19,396 width=162) (actual rows= loops=)

  • Join Filter: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
19. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_people ref_people_1 (cost=791.08..14,938.13 rows=4,230 width=47) (actual rows= loops=)

  • Recheck Cond: is_active
  • Filter: ((id_statut <> 2) AND (COALESCE(id_qualification, 1) >= 1) AND (id_type = 5))
20. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_people_xperf08 (cost=0.00..790.02 rows=40,346 width=0) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Materialize (cost=4.43..48.92 rows=5 width=55) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on top_challenge_list top_challenge_list_1 (cost=4.43..48.90 rows=5 width=55) (actual rows= loops=)

  • Recheck Cond: (id_int_manager IS NULL)
  • Filter: (is_active AND ((template_challenge)::text = 'top_sales'::text))
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on top_challenge_list_xperf02 (cost=0.00..4.43 rows=20 width=0) (actual rows= loops=)

  • Index Cond: (id_int_manager IS NULL)
24.          

SubPlan (forNested Loop)

25. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,825.16..4,825.17 rows=1 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_2 (cost=29.73..4,825.15 rows=1 width=0) (actual rows= loops=)

  • Recheck 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))
27. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf03 (cost=0.00..29.73 rows=1,239 width=0) (actual rows= loops=)

  • Index Cond: (id_int_agent_in = ref_people_1.id_int)
28. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,730.03..4,730.04 rows=1 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_3 (cost=29.54..4,730.02 rows=1 width=0) (actual rows= loops=)

  • Recheck 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))
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf04 (cost=0.00..29.54 rows=1,214 width=0) (actual rows= loops=)