explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RIIh

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 458.010 ↑ 1.0 5 1

Limit (cost=1.26..9,422.38 rows=5 width=170) (actual time=0.306..458.010 rows=5 loops=1)

2. 0.136 457.999 ↑ 14.8 5 1

Nested Loop (cost=1.26..139,433.86 rows=74 width=170) (actual time=0.304..457.999 rows=5 loops=1)

3. 0.006 0.143 ↑ 16.6 5 1

Nested Loop (cost=0.84..2,824.34 rows=83 width=83) (actual time=0.134..0.143 rows=5 loops=1)

4. 0.004 0.082 ↑ 12.0 1 1

Nested Loop (cost=0.42..2,634.48 rows=12 width=83) (actual time=0.082..0.082 rows=1 loops=1)

5. 0.027 0.027 ↑ 109.0 1 1

Seq Scan on top_challenge_list (cost=0.00..84.72 rows=109 width=55) (actual time=0.026..0.027 rows=1 loops=1)

  • Filter: (is_active AND ((template_challenge)::text = 'top_turnover'::text))
  • Rows Removed by Filter: 4
6. 0.051 0.051 ↑ 1.0 1 1

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

  • 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))
7. 0.055 0.055 ↑ 2.4 5 1

Index Scan using ref_genealogy_xperf5 on ref_genealogy (cost=0.42..15.70 rows=12 width=8) (actual time=0.049..0.055 rows=5 loops=1)

  • Index Cond: (id_int = ref_people.id_int)
  • Filter: is_active
8. 0.370 0.370 ↑ 1.0 1 5

Index Scan using ref_people_xperf01 on ref_people filleuls (cost=0.42..0.73 rows=1 width=47) (actual time=0.051..0.074 rows=1 loops=5)

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

SubPlan (forNested Loop)

10. 0.035 233.060 ↑ 1.0 1 5

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

11. 1.120 233.025 ↓ 0.0 0 5

Bitmap Heap Scan on ref_transaction (cost=902.45..922.50 rows=1 width=8) (actual time=46.601..46.605 rows=0 loops=5)

  • 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))
  • Rows Removed by Index Recheck: 1
  • Filter: (is_active AND (id_type_transaction = 1))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=51
12. 2.607 231.905 ↓ 0.0 0 5

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

13. 0.120 0.120 ↑ 43.4 28 5

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

  • Index Cond: (id_int_agent_out = filleuls.id_int)
14. 229.178 229.178 ↓ 48.9 1,597,692 1

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

  • 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))
15. 0.045 224.290 ↑ 1.0 1 5

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

16. 0.035 224.245 ↓ 0.0 0 5

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

  • 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))
  • Rows Removed by Index Recheck: 1
  • Filter: (is_active AND (id_type_transaction = 1))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=7
17. 1.944 224.210 ↓ 0.0 0 5

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

18. 0.115 0.115 ↑ 95.3 13 5

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

  • Index Cond: (id_int_agent_in = filleuls.id_int)
19. 222.151 222.151 ↓ 48.9 1,597,692 1

Bitmap Index Scan on ref_transaction_xperf12 (cost=0.00..872.66 rows=32,658 width=0) (actual time=222.150..222.151 rows=1,597,692 loops=1)

  • 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))