explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dBha : Optimization for: Optimization for: plan #RIIh; plan #EOWH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13.697 38,597.523 ↓ 3.9 40,578 1

Append (cost=6.00..7,641,238.45 rows=10,356 width=172) (actual time=1.354..38,597.523 rows=40,578 loops=1)

2. 673.295 26,935.392 ↓ 424.4 26,314 1

Nested Loop (cost=6.00..48,074.61 rows=62 width=172) (actual time=1.354..26,935.392 rows=26,314 loops=1)

3. 26.080 210.514 ↓ 462.9 30,091 1

Nested Loop (cost=5.57..2,419.22 rows=65 width=85) (actual time=1.286..210.514 rows=30,091 loops=1)

4. 0.319 34.339 ↓ 11.0 55 1

Nested Loop (cost=5.15..2,355.40 rows=5 width=85) (actual time=1.272..34.339 rows=55 loops=1)

5. 0.792 1.924 ↓ 1.0 59 1

Bitmap Heap Scan on top_challenge_list (cost=4.73..29.47 rows=58 width=57) (actual time=1.210..1.924 rows=59 loops=1)

  • Recheck Cond: ((template_challenge)::text = 'top_turnover'::text)
  • Filter: is_active
  • Heap Blocks: exact=22
6. 1.132 1.132 ↑ 1.0 59 1

Bitmap Index Scan on top_challenge_list_xperf1 (cost=0.00..4.71 rows=59 width=0) (actual time=1.132..1.132 rows=59 loops=1)

  • Index Cond: ((template_challenge)::text = 'top_turnover'::text)
7. 32.096 32.096 ↑ 1.0 1 59

Index Scan using ref_people_xperf1 on ref_people (cost=0.42..40.09 rows=1 width=28) (actual time=0.528..0.544 rows=1 loops=59)

  • 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
8. 150.095 150.095 ↓ 27.4 547 55

Index Scan using ref_genealogy_xperf5 on ref_genealogy (cost=0.42..12.56 rows=20 width=8) (actual time=0.907..2.729 rows=547 loops=55)

  • Index Cond: (id_int = ref_people.id_int)
  • Filter: is_active
9. 7,552.841 7,552.841 ↑ 1.0 1 30,091

Index Scan using ref_people_xperf1 on ref_people filleuls (cost=0.42..0.82 rows=1 width=47) (actual time=0.091..0.251 rows=1 loops=30,091)

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

SubPlan (forNested Loop)

11. 78.942 14,314.816 ↑ 1.0 1 26,314

Aggregate (cost=361.46..361.47 rows=1 width=8) (actual time=0.543..0.544 rows=1 loops=26,314)

12. 14,235.874 14,235.874 ↑ 1.0 1 26,314

Index Scan using ref_transaction_xperf4 on ref_transaction (cost=0.42..361.46 rows=1 width=8) (actual time=0.205..0.541 rows=1 loops=26,314)

  • Index Cond: (id_int_agent_out = filleuls.id_int)
  • Filter: (is_active AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge) AND (id_type_transaction = 1))
  • Rows Removed by Filter: 24
13. 52.628 4,183.926 ↑ 1.0 1 26,314

Aggregate (cost=373.99..374.00 rows=1 width=8) (actual time=0.159..0.159 rows=1 loops=26,314)

14. 4,131.298 4,131.298 ↑ 1.0 1 26,314

Index Scan using ref_transaction_xperf3 on ref_transaction ref_transaction_1 (cost=0.42..373.99 rows=1 width=8) (actual time=0.074..0.157 rows=1 loops=26,314)

  • Index Cond: (id_int_agent_in = filleuls.id_int)
  • Filter: (is_active AND (date_acte IS NOT NULL) AND (date_acte >= top_challenge_list.date_deb_challenge) AND (date_acte <= top_challenge_list.date_fin_challenge) AND (id_type_transaction = 1))
  • Rows Removed by Filter: 24
15. 329.648 11,648.434 ↓ 1.4 14,264 1

Nested Loop (cost=1,000.27..7,593,008.50 rows=10,294 width=172) (actual time=10.866..11,648.434 rows=14,264 loops=1)

  • Join Filter: (ref_people_1.id_filiale = top_challenge_list_1.id_filiale)
  • Rows Removed by Join Filter: 1308
16. 6.142 6.142 ↑ 1.5 2 1

Index Scan using top_challenge_xperf4 on top_challenge_list top_challenge_list_1 (cost=0.27..52.04 rows=3 width=57) (actual time=0.797..6.142 rows=2 loops=1)

  • Filter: ((id_int_manager IS NULL) AND is_active AND ((template_challenge)::text = 'top_turnover'::text))
  • Rows Removed by Filter: 499
17. 10.565 29.820 ↓ 2.2 7,786 2

Materialize (cost=1,000.00..26,301.48 rows=3,611 width=47) (actual time=4.943..14.910 rows=7,786 loops=2)

18. 0.000 19.255 ↓ 2.2 7,786 1

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

  • Workers Planned: 2
  • Workers Launched: 2
19. 293.863 293.863 ↓ 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=0.670..293.863 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
20.          

SubPlan (forNested Loop)

21. 42.792 8,372.968 ↑ 1.0 1 14,264

Aggregate (cost=361.23..361.24 rows=1 width=8) (actual time=0.587..0.587 rows=1 loops=14,264)

22. 8,330.176 8,330.176 ↓ 2.0 2 14,264

Index Scan using ref_transaction_xperf4 on ref_transaction ref_transaction_2 (cost=0.42..361.23 rows=1 width=8) (actual time=0.306..0.584 rows=2 loops=14,264)

  • Index Cond: (id_int_agent_out = ref_people_1.id_int)
  • Filter: (is_active 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))
  • Rows Removed by Filter: 66
23. 28.528 2,909.856 ↑ 1.0 1 14,264

Aggregate (cost=373.75..373.77 rows=1 width=8) (actual time=0.204..0.204 rows=1 loops=14,264)

24. 2,881.328 2,881.328 ↓ 2.0 2 14,264

Index Scan using ref_transaction_xperf3 on ref_transaction ref_transaction_3 (cost=0.42..373.75 rows=1 width=8) (actual time=0.113..0.202 rows=2 loops=14,264)

  • Index Cond: (id_int_agent_in = ref_people_1.id_int)
  • Filter: (is_active 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))
  • Rows Removed by Filter: 66
Planning time : 7.865 ms