explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uysm : Konstantin's request

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.340 262,714.697 ↓ 110.0 110 1

Sort (cost=24,052.53..24,052.53 rows=1 width=57) (actual time=262,714.661..262,714.697 rows=110 loops=1)

  • Sort Key: dim_companies.company_key
  • Sort Method: quicksort Memory: 40kB
2. 7,587.606 262,714.357 ↓ 110.0 110 1

Nested Loop Left Join (cost=7,493.72..24,052.52 rows=1 width=57) (actual time=5,714.147..262,714.357 rows=110 loops=1)

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

Gather (cost=1,000.00..10,729.80 rows=1 width=25) (actual time=0.470..8.626 rows=2,575 loops=1)

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

Parallel Seq Scan on dim_companies (cost=0.00..9,729.70 rows=1 width=25) (actual time=0.031..74.745 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: 51050
5. 29,053.725 255,118.125 ↓ 3.5 5,981 2,575

Gather (cost=6,493.72..13,301.31 rows=1,712 width=8) (actual time=0.989..99.075 rows=5,981 loops=2,575)

  • Workers Planned: 2
  • Workers Launched: 2
6. 9,169.575 226,064.400 ↓ 2.8 1,994 7,725 / 3

Nested Loop (cost=5,493.72..12,130.11 rows=713 width=8) (actual time=59.855..87.792 rows=1,994 loops=7,725)

7. 7,832.537 201,493.750 ↓ 2.8 1,994 7,725 / 3

Hash Join (cost=5,493.30..10,278.32 rows=713 width=8) (actual time=59.830..78.250 rows=1,994 loops=7,725)

  • Hash Cond: (fcurp.fcur_key = fcur.fcur_key)
8. 40,180.300 40,180.300 ↑ 1.3 2,345 7,725 / 3

Parallel Seq Scan on fact_company_user_role_positions fcurp (cost=0.00..4,777.23 rows=2,969 width=4) (actual time=0.014..15.604 rows=2,345 loops=7,725)

  • Filter: ((position_name)::text = 'Controller'::text)
  • Rows Removed by Filter: 65574
9. 44,051.352 153,480.913 ↑ 1.0 40,789 5,151 / 3

Hash (cost=4,981.95..4,981.95 rows=40,908 width=12) (actual time=89.389..89.389 rows=40,789 loops=5,151)

  • Buckets: 65536 Batches: 1 Memory Usage: 2265kB
10. 109,429.561 109,429.561 ↑ 1.0 40,789 5,151 / 3

Seq Scan on fact_company_user_roles fcur (cost=0.00..4,981.95 rows=40,908 width=12) (actual time=0.142..63.733 rows=40,789 loops=5,151)

  • Filter: ((fcur_status)::text = ANY ('{active,invited}'::text[]))
  • Rows Removed by Filter: 129447
11. 15,401.075 15,401.075 ↑ 1.0 1 15,401,075 / 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,401,075)

  • Index Cond: (user_key = fcur.user_key)
  • Heap Fetches: 0
Planning time : 1.598 ms
Execution time : 262,715.856 ms