explain.depesz.com

PostgreSQL's explain analyze made readable

Result: neWx : Konstantin's request; plan #Uysm Added index fcur_status; plan #yyLk added multiindex

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.370 240,218.973 ↓ 110.0 110 1

Sort (cost=23,592.71..23,592.71 rows=1 width=57) (actual time=240,218.927..240,218.973 rows=110 loops=1)

  • Sort Key: dim_companies.company_key
  • Sort Method: quicksort Memory: 40kB
2. 8,092.467 240,218.603 ↓ 110.0 110 1

Nested Loop Left Join (cost=7,105.67..23,592.70 rows=1 width=57) (actual time=4,314.946..240,218.603 rows=110 loops=1)

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

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

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

Parallel Seq Scan on dim_companies (cost=0.00..9,729.88 rows=1 width=25) (actual time=0.036..42.618 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,065
5. 29,710.350 232,115.650 ↓ 3.6 5,979 2,575

Gather (cost=6,105.67..12,842.11 rows=1,647 width=8) (actual time=0.980..90.142 rows=5,979 loops=2,575)

  • Workers Planned: 2
  • Workers Launched: 2
6. 11,245.025 202,405.300 ↓ 2.9 1,993 7,725 / 3

Nested Loop (cost=5,105.67..11,677.41 rows=686 width=8) (actual time=48.113..78.604 rows=1,993 loops=7,725)

7. 8,673.581 175,764.350 ↓ 2.9 1,993 7,725 / 3

Hash Join (cost=5,105.25..9,892.50 rows=686 width=8) (actual time=48.084..68.258 rows=1,993 loops=7,725)

  • Hash Cond: (fcurp.fcur_key = fcur.fcur_key)
8. 43,926.925 43,926.925 ↑ 1.2 2,344 7,725 / 3

Parallel Seq Scan on fact_company_user_role_positions fcurp (cost=0.00..4,779.72 rows=2,871 width=4) (actual time=0.019..17.059 rows=2,344 loops=7,725)

  • Filter: ((position_name)::text = 'Controller'::text)
  • Rows Removed by Filter: 65,606
9. 54,866.735 123,163.844 ↓ 1.0 40,798 5,151 / 3

Hash (cost=4,596.31..4,596.31 rows=40,715 width=12) (actual time=71.732..71.732 rows=40,798 loops=5,151)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,266kB
10. 58,285.282 68,297.109 ↓ 1.0 40,798 5,151 / 3

Bitmap Heap Scan on fact_company_user_roles fcur (cost=1,232.37..4,596.31 rows=40,715 width=12) (actual time=6.077..39.777 rows=40,798 loops=5,151)

  • Recheck Cond: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
  • Heap Blocks: exact=1,435
11. 10,011.827 10,011.827 ↓ 1.0 40,798 5,151 / 3

Bitmap Index Scan on fact_company_user_roles_fcur_status (cost=0.00..1,222.19 rows=40,715 width=0) (actual time=5.831..5.831 rows=40,798 loops=5,151)

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

Index Only Scan using dim_users_pkey on dim_users (cost=0.42..2.60 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=15,395,925)

  • Index Cond: (user_key = fcur.user_key)
  • Heap Fetches: 0
Planning time : 0.809 ms
Execution time : 240,220.289 ms