explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aQ2 : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 0.262 ↓ 6.0 6 1

Hash Join (cost=170.68..237.12 rows=1 width=5,684) (actual time=0.160..0.262 rows=6 loops=1)

  • Output: c.client_key, c.source_system, c.client_type, c.client_geography, c.industry, c.structure, r.account_role, a.account_number, a.application_code, r.acquisition_channel, a.ac
  • Hash Cond: ((r.account_key)::text = (a.account_key)::text)
  • Buffers: shared hit=98
2.          

CTE clients_rescore

3. 0.015 0.057 ↑ 1.0 8 1

Nested Loop (cost=0.29..67.60 rows=8 width=32) (actual time=0.013..0.057 rows=8 loops=1)

  • Output: c_1.client_key, c_1.source_system, c_1.client_type, c_1.client_geography, c_1.industry, c_1.structure
  • Buffers: shared hit=25
4. 0.002 0.002 ↑ 1.0 8 1

Seq Scan on public.clients_fa_tmp cf (cost=0.00..1.08 rows=8 width=13) (actual time=0.002..0.002 rows=8 loops=1)

  • Output: cf.client_key
  • Buffers: shared hit=1
5. 0.040 0.040 ↑ 1.0 1 8

Index Scan using clients_pkey on public.clients c_1 (cost=0.29..8.30 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=8)

  • Output: c_1.client_key, c_1.client_type, c_1.client_name, c_1.tax_identifier, c_1.client_geography, c_1.pep, c_1.sars, c_1.industry, c_1.structure, c_1.source_system,
  • Index Cond: ((c_1.client_key)::text = (cf.client_key)::text)
  • Buffers: shared hit=24
6.          

CTE accounts_rescore

7. 0.020 0.101 ↑ 1.0 12 1

Nested Loop (cost=0.41..102.40 rows=12 width=54) (actual time=0.019..0.101 rows=12 loops=1)

  • Output: a_1.account_key, a_1.account_number, a_1.application_code, a_1.account_type_product, a_1.account_status, a_1.account_geography
  • Buffers: shared hit=49
8. 0.009 0.009 ↑ 1.0 12 1

Seq Scan on public.accounts_fa_tmp af (cost=0.00..1.12 rows=12 width=23) (actual time=0.005..0.009 rows=12 loops=1)

  • Output: af.account_key
  • Buffers: shared hit=1
9. 0.072 0.072 ↑ 1.0 1 12

Index Scan using accounts_pkey on public.accounts a_1 (cost=0.41..8.43 rows=1 width=54) (actual time=0.006..0.006 rows=1 loops=12)

  • Output: a_1.account_key, a_1.account_number, a_1.application_code, a_1.account_type_product, a_1.account_status, a_1.account_geography, a_1.anticipated_activity, a_1.
  • Index Cond: ((a_1.account_key)::text = (af.account_key)::text)
  • Buffers: shared hit=48
10. 0.008 0.121 ↑ 1.4 8 1

Nested Loop (cost=0.29..66.68 rows=11 width=3,127) (actual time=0.025..0.121 rows=8 loops=1)

  • Output: c.client_key, c.source_system, c.client_type, c.client_geography, c.industry, c.structure, r.account_role, r.acquisition_channel, r.account_key
  • Buffers: shared hit=49
11. 0.065 0.065 ↑ 1.0 8 1

CTE Scan on clients_rescore c (cost=0.00..0.16 rows=8 width=3,096) (actual time=0.013..0.065 rows=8 loops=1)

  • Output: c.client_key, c.source_system, c.client_type, c.client_geography, c.industry, c.structure
  • Buffers: shared hit=25
12. 0.048 0.048 ↑ 1.0 1 8

Index Scan using idx_clientaccounts_client_key on public.client_accounts r (cost=0.29..8.30 rows=1 width=44) (actual time=0.005..0.006 rows=1 loops=8)

  • Output: r.account_key, r.client_key, r.account_role, r.acquisition_channel, r.source_system, r.effective_period, r.source_created_at, r.source_updated_at
  • Index Cond: ((r.client_key)::text = (c.client_key)::text)
  • Buffers: shared hit=24
13. 0.008 0.121 ↑ 1.0 12 1

Hash (cost=0.24..0.24 rows=12 width=3,096) (actual time=0.121..0.121 rows=12 loops=1)

  • Output: a.account_number, a.application_code, a.account_type_product, a.account_status, a.account_geography, a.account_key
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=49
14. 0.113 0.113 ↑ 1.0 12 1

CTE Scan on accounts_rescore a (cost=0.00..0.24 rows=12 width=3,096) (actual time=0.022..0.113 rows=12 loops=1)

  • Output: a.account_number, a.application_code, a.account_type_product, a.account_status, a.account_geography, a.account_key
  • Buffers: shared hit=49