explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WB7w

Settings

Optimization(s) for this plan:

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

Unique (cost=14,472,276.81..14,472,687.99 rows=7,832 width=518) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=14,472,276.81..14,472,296.39 rows=7,832 width=518) (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(((COALESCE((SubPlan 1), '0'::double precision))::numeric(10,2) + (COALESCE((SubPlan 2), '0'::double precision))::numeric(10,2)), '0'::numeric))::numeric(10,2)), (0)
3. 0.000 0.000 ↓ 0.0

Append (cost=10.79..14,469,946.77 rows=7,832 width=518) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.79..139,428.70 rows=74 width=170) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.37..2,819.18 rows=83 width=83) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.95..2,629.32 rows=12 width=83) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on top_challenge_list (cost=9.53..79.56 rows=109 width=55) (actual rows= loops=)

  • Recheck Cond: ((template_challenge)::text = 'top_turnover'::text)
  • Filter: is_active
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on top_challenge_list_xperf05 (cost=0.00..9.50 rows=163 width=0) (actual rows= loops=)

  • Index Cond: ((template_challenge)::text = 'top_turnover'::text)
9. 0.000 0.000 ↓ 0.0

Index Scan using ref_people_xperf01 on ref_people (cost=0.42..23.38 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))
10. 0.000 0.000 ↓ 0.0

Index Scan using ref_genealogy_xperf5 on ref_genealogy (cost=0.42..15.70 rows=12 width=8) (actual rows= loops=)

  • Index Cond: (id_int = ref_people.id_int)
  • Filter: is_active
11. 0.000 0.000 ↓ 0.0

Index Scan using ref_people_xperf01 on ref_people filleuls (cost=0.42..0.73 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[])))
12.          

SubPlan (forNested Loop)

13. 0.000 0.000 ↓ 0.0

Aggregate (cost=922.50..922.51 rows=1 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction (cost=902.45..922.50 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((id_int_agent_out = filleuls.id_int) AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge))
  • Filter: (is_active AND (id_type_transaction = 1))
15. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=902.45..902.45 rows=5 width=0) (actual rows= loops=)

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

Bitmap Index Scan on ref_transaction_xperf12 (cost=0.00..872.66 rows=32,658 width=0) (actual rows= loops=)

  • Index Cond: ((date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge))
18. 0.000 0.000 ↓ 0.0

Aggregate (cost=922.69..922.70 rows=1 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_1 (cost=902.63..922.68 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((id_int_agent_in = filleuls.id_int) AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge))
  • Filter: (is_active AND (id_type_transaction = 1))
20. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=902.63..902.63 rows=5 width=0) (actual rows= loops=)

21. 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)
22. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf12 (cost=0.00..872.66 rows=32,658 width=0) (actual rows= loops=)

  • Index Cond: ((date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge))
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=791.36..14,330,400.59 rows=7,758 width=170) (actual rows= loops=)

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

Index Scan using top_challenge_list_xperf03 on top_challenge_list top_challenge_list_1 (cost=0.28..122.69 rows=2 width=55) (actual rows= loops=)

  • Filter: ((id_int_manager IS NULL) AND is_active AND ((template_challenge)::text = 'top_turnover'::text))
25. 0.000 0.000 ↓ 0.0

Materialize (cost=791.08..14,959.28 rows=4,230 width=47) (actual rows= loops=)

26. 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))
27. 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=)

28.          

SubPlan (forNested Loop)

29. 0.000 0.000 ↓ 0.0

Aggregate (cost=922.49..922.50 rows=1 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_2 (cost=902.45..922.48 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((id_int_agent_out = ref_people_1.id_int) AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list_1.date_deb_challenge) AND (date_acte <= top_challenge_list_1.date_fin_challenge))
  • Filter: is_active
31. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=902.45..902.45 rows=5 width=0) (actual rows= loops=)

32. 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 = ref_people_1.id_int)
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf12 (cost=0.00..872.66 rows=32,658 width=0) (actual rows= loops=)

  • Index Cond: ((date_acte IS NOT NULL) AND (date_acte >= top_challenge_list_1.date_deb_challenge) AND (date_acte <= top_challenge_list_1.date_fin_challenge))
34. 0.000 0.000 ↓ 0.0

Aggregate (cost=922.67..922.69 rows=1 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ref_transaction ref_transaction_3 (cost=902.63..922.67 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((id_int_agent_in = ref_people_1.id_int) AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list_1.date_deb_challenge) AND (date_acte <= top_challenge_list_1.date_fin_challenge))
  • Filter: is_active
36. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=902.63..902.63 rows=5 width=0) (actual rows= loops=)

37. 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)
38. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ref_transaction_xperf12 (cost=0.00..872.66 rows=32,658 width=0) (actual rows= loops=)