explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zcIl : Rewrite with subquery; plan #Uysm Added index fcur_status; plan #yyLk added multiindex ; plan #neWx; plan #c0ej

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.228 10,561.338 ↓ 110.0 110 1

Sort (cost=24,344.32..24,344.32 rows=1 width=57) (actual time=10,561.309..10,561.338 rows=110 loops=1)

  • Sort Key: dim_companies.company_key
  • Sort Method: quicksort Memory: 40kB
2.          

CTE users

3. 3.892 114.495 ↓ 3.5 5,979 1

Gather (cost=6,492.91..13,559.13 rows=1,692 width=1,147) (actual time=89.332..114.495 rows=5,979 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 4.421 110.603 ↓ 2.8 1,993 3 / 3

Nested Loop (cost=5,492.91..12,389.93 rows=705 width=1,147) (actual time=85.488..110.603 rows=1,993 loops=3)

5. 2.412 100.203 ↓ 2.8 1,993 3 / 3

Hash Join (cost=5,492.49..10,281.23 rows=705 width=8) (actual time=85.467..100.203 rows=1,993 loops=3)

  • Hash Cond: (fcurp.fcur_key = fcur.fcur_key)
6. 12.596 12.596 ↑ 1.3 2,344 3 / 3

Parallel Seq Scan on fact_company_user_role_positions fcurp (cost=0.00..4,781.01 rows=2,948 width=4) (actual time=0.007..12.596 rows=2,344 loops=3)

  • Filter: ((position_name)::text = 'Controller'::text)
  • Rows Removed by Filter: 65,616
7. 26.079 85.195 ↓ 1.0 40,802 3 / 3

Hash (cost=4,983.55..4,983.55 rows=40,715 width=12) (actual time=85.195..85.195 rows=40,802 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,266kB
8. 59.116 59.116 ↓ 1.0 40,802 3 / 3

Seq Scan on fact_company_user_roles fcur (cost=0.00..4,983.55 rows=40,715 width=12) (actual time=0.032..59.116 rows=40,802 loops=3)

  • Filter: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
  • Rows Removed by Filter: 129,498
9. 5.979 5.979 ↑ 1.0 1 5,979 / 3

Index Scan using dim_users_pkey on dim_users (cost=0.42..2.99 rows=1 width=1,143) (actual time=0.003..0.003 rows=1 loops=5,979)

  • Index Cond: (user_key = fcur.user_key)
10. 6,442.354 10,561.110 ↓ 110.0 110 1

Nested Loop Left Join (cost=1,000.00..10,785.18 rows=1 width=57) (actual time=190.169..10,561.110 rows=110 loops=1)

  • Join Filter: (dim_companies.company_key = users.company_key)
  • Rows Removed by Join Filter: 15,391,743
  • Filter: (users.user_key IS NULL)
  • Rows Removed by Filter: 4,182
11. 0.000 6.481 ↓ 2,575.0 2,575 1

Gather (cost=1,000.00..10,730.18 rows=1 width=25) (actual time=0.343..6.481 rows=2,575 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 40.644 40.644 ↓ 858.0 858 3 / 3

Parallel Seq Scan on dim_companies (cost=0.00..9,730.08 rows=1 width=25) (actual time=0.038..40.644 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: 51,070
13. 4,112.275 4,112.275 ↓ 3.5 5,979 2,575

CTE Scan on users (cost=0.00..33.84 rows=1,692 width=8) (actual time=0.035..1.597 rows=5,979 loops=2,575)

Planning time : 0.527 ms
Execution time : 10,561.808 ms