explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iksK : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #EnRL; plan #HHM9; plan #FxYF; plan #AU47; plan #SQBN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 36.274 9,848.813 ↑ 3.0 1 1

HashAggregate (cost=42,438.43..42,438.46 rows=3 width=15) (actual time=9,848.813..9,848.813 rows=1 loops=1)

  • Group Key: user0_.state
2. 22.706 9,812.539 ↓ 3.4 95,409 1

Nested Loop Semi Join (cost=7,262.53..42,298.86 rows=27,913 width=15) (actual time=324.761..9,812.539 rows=95,409 loops=1)

3. 65.726 7,499.945 ↓ 3.4 95,412 1

Nested Loop (cost=7,261.97..20,873.52 rows=27,913 width=23) (actual time=324.729..7,499.945 rows=95,412 loops=1)

4. 87.844 373.731 ↓ 3.4 95,412 1

HashAggregate (cost=7,261.54..7,540.67 rows=27,913 width=8) (actual time=324.706..373.731 rows=95,412 loops=1)

  • Group Key: providerli1_.user_id
5. 9.133 285.887 ↓ 3.8 104,930 1

Nested Loop (cost=314.18..7,191.76 rows=27,913 width=8) (actual time=60.668..285.887 rows=104,930 loops=1)

6. 0.029 0.029 ↑ 1.0 1 1

Index Scan using provider_laboratory_id on provider provider2_ (cost=0.14..0.76 rows=1 width=8) (actual time=0.027..0.029 rows=1 loops=1)

  • Index Cond: (laboratory_id = 6)
7. 223.441 276.725 ↓ 2.9 104,930 1

Bitmap Heap Scan on provider_link providerli1_ (cost=314.04..6,834.07 rows=35,693 width=16) (actual time=60.618..276.725 rows=104,930 loops=1)

  • Recheck Cond: (provider_id = provider2_.id)
  • Filter: ((link_state)::text = ANY ('{ACTIVE,INACTIVE}'::text[]))
  • Rows Removed by Filter: 782
  • Heap Blocks: exact=42,584
8. 53.284 53.284 ↓ 2.9 105,841 1

Bitmap Index Scan on idx_provider_link_provider_id (cost=0.00..305.12 rows=36,025 width=0) (actual time=53.284..53.284 rows=105,841 loops=1)

  • Index Cond: (provider_id = provider2_.id)
9. 7,060.488 7,060.488 ↑ 1.0 1 95,412

Index Scan using bwuser_pkey on bwuser user0_ (cost=0.43..0.47 rows=1 width=15) (actual time=0.074..0.074 rows=1 loops=95,412)

  • Index Cond: (id = providerli1_.user_id)
10. 95.415 2,289.888 ↑ 1.0 1 95,412

Nested Loop (cost=0.56..0.79 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=95,412)

11. 2,099.064 2,099.064 ↑ 2.0 1 95,412

Index Scan using idx_user_profile_user_id on user_profile userprofil3_ (cost=0.43..0.47 rows=2 width=16) (actual time=0.022..0.022 rows=1 loops=95,412)

  • Index Cond: (user_id = providerli1_.user_id)
12. 95.409 95.409 ↑ 1.0 1 95,409

Index Scan using profile_pkey on profile profile4_ (cost=0.13..0.15 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=95,409)

  • Index Cond: (id = userprofil3_.profile_id)
  • Filter: ((code)::text <> ALL ('{ADMINISTRATEUR_LABOCONNECT,ADMINISTRATEUR_LABO}'::text[]))
Planning time : 27.617 ms
Execution time : 9,850.662 ms