explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c0ej : Reset where to bottom; plan #Uysm Added index fcur_status; plan #yyLk added multiindex ; plan #neWx

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 0.024 ↓ 0.0 0 1

Sort (cost=4,710.75..4,710.76 rows=1 width=57) (actual time=0.024..0.024 rows=0 loops=1)

  • Sort Key: dim_companies.company_key
  • Sort Method: quicksort Memory: 25kB
2. 0.001 0.017 ↓ 0.0 0 1

Nested Loop (cost=1,237.66..4,710.74 rows=1 width=57) (actual time=0.016..0.017 rows=0 loops=1)

3. 0.001 0.016 ↓ 0.0 0 1

Nested Loop (cost=1,237.24..4,708.17 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)

4. 0.006 0.015 ↓ 0.0 0 1

Hash Join (cost=1,236.82..4,707.64 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)

  • Hash Cond: (fcur.user_key = dim_users.user_key)
5. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on fact_company_user_roles fcur (cost=1,232.37..4,596.31 rows=40,715 width=12) (never executed)

  • Recheck Cond: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
6. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on fact_company_user_roles_fcur_status (cost=0.00..1,222.19 rows=40,715 width=0) (never executed)

  • Index Cond: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
7. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=4.44..4.44 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
8. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using dim_users_pkey on dim_users (cost=0.42..4.44 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (user_key IS NULL)
  • Heap Fetches: 0
9. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_company_user_role_positions_multi_key_position on fact_company_user_role_positions fcurp (cost=0.42..0.52 rows=1 width=4) (never executed)

  • Index Cond: ((fcur_key = fcur.fcur_key) AND (position_name = 'Controller'::text))
  • Heap Fetches: 0
10. 0.000 0.000 ↓ 0.0 0

Index Scan using dim_companies_pkey on dim_companies (cost=0.42..1.49 rows=1 width=25) (never executed)

  • Index Cond: (company_key = fcur.company_key)
  • Filter: ((became_customer_at IS NOT NULL) AND ((app_status)::text <> 'archived'::text) AND ((legal_status)::text <> ALL ('{"Struck off","Gazetted to be struck off"}'::text[])) AND ((deal_status)::text = 'won'::text) AND ((branch_country_key)::text = 'SG'::text))
Planning time : 1.082 ms
Execution time : 6.264 ms