explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yyLk : Optimization for: Konstantin's request; plan #Uysm Added index fcur_status

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.348 178,543.071 ↓ 110.0 110 1

Sort (cost=23,147.46..23,147.47 rows=1 width=57) (actual time=178,543.025..178,543.071 rows=110 loops=1)

  • Sort Key: dim_companies.company_key
  • Sort Method: quicksort Memory: 40kB
2. 6,772.789 178,542.723 ↓ 110.0 110 1

Nested Loop Left Join (cost=6,633.29..23,147.45 rows=1 width=57) (actual time=2,657.334..178,542.723 rows=110 loops=1)

  • Join Filter: (dim_companies.company_key = fcur.company_key)
  • Rows Removed by Join Filter: 15394318
  • Filter: (dim_users.user_key IS NULL)
  • Rows Removed by Filter: 4182
3. 0.000 9.709 ↓ 2,575.0 2,575 1

Gather (cost=1,000.00..10,729.98 rows=1 width=25) (actual time=0.458..9.709 rows=2,575 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 32.161 32.161 ↓ 858.0 858 3 / 3

Parallel Seq Scan on dim_companies (cost=0.00..9,729.88 rows=1 width=25) (actual time=0.059..32.161 rows=858 loops=3)

  • 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))
  • Rows Removed by Filter: 51057
5. 12,280.175 171,760.225 ↓ 3.6 5,980 2,575

Gather (cost=5,633.29..12,396.42 rows=1,683 width=8) (actual time=1.078..66.703 rows=5,980 loops=2,575)

  • Workers Planned: 2
  • Workers Launched: 2
6. 6,450.375 159,480.050 ↓ 2.8 1,993 7,725 / 3

Nested Loop (cost=4,633.29..11,228.12 rows=701 width=8) (actual time=37.029..61.934 rows=1,993 loops=7,725)

7. 6,903.204 137,631.175 ↓ 2.8 1,993 7,725 / 3

Hash Join (cost=4,632.87..9,418.87 rows=701 width=8) (actual time=37.007..53.449 rows=1,993 loops=7,725)

  • Hash Cond: (fcurp.fcur_key = fcur.fcur_key)
8. 35,970.175 35,970.175 ↑ 1.2 2,345 7,725 / 3

Parallel Seq Scan on fact_company_user_role_positions fcurp (cost=0.00..4,778.47 rows=2,870 width=4) (actual time=0.007..13.969 rows=2,345 loops=7,725)

  • Filter: ((position_name)::text = 'Controller'::text)
  • Rows Removed by Filter: 65589
9. 42,588.468 94,757.796 ↑ 1.0 40,800 5,151 / 3

Hash (cost=4,113.01..4,113.01 rows=41,589 width=12) (actual time=55.187..55.188 rows=40,800 loops=5,151)

  • Buckets: 65536 Batches: 1 Memory Usage: 2266kB
10. 45,493.632 52,169.328 ↑ 1.0 40,800 5,151 / 3

Bitmap Heap Scan on fact_company_user_roles fcur (cost=739.15..4,113.01 rows=41,589 width=12) (actual time=4.102..30.384 rows=40,800 loops=5,151)

  • Recheck Cond: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
  • Heap Blocks: exact=1436
11. 6,675.696 6,675.696 ↑ 1.0 40,800 5,151 / 3

Bitmap Index Scan on fact_company_user_roles_fcur_status (cost=0.00..728.75 rows=41,589 width=0) (actual time=3.888..3.888 rows=40,800 loops=5,151)

  • Index Cond: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
12. 15,398.500 15,398.500 ↑ 1.0 1 15,398,500 / 3

Index Only Scan using dim_users_pkey on dim_users (cost=0.42..2.58 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=15,398,500)

  • Index Cond: (user_key = fcur.user_key)
  • Heap Fetches: 13374
Planning time : 0.608 ms
Execution time : 178,543.826 ms