explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 76uQ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7,805.861 102,867.715 ↓ 86,211.0 86,211 1

Hash Join (cost=79,995.56..79,998.34 rows=1 width=2,248) (actual time=95,150.888..102,867.715 rows=86,211 loops=1)

  • Output: filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.legal_entity_family_id, filterkeys.legal_entity_id, filterkeys.gbo_type_id, filterkeys.admin_pm, filterkeys.book_id, filterkeys.file_id, filterkeys.account_group_category, filterkeys.account_type, filterkeys.strategy_id, filterkeys.demux_group_id, filterkeys.financial_account, filterkeys.currency,
  • filterkeys.admin_account, filterkeys.custodian_account_id, filterkeys.gbo_type_group, filterkeys.trade_date_opening_bal, filterkeys.opening_balance_mtd_local, filterkeys.settle_date_closing_bal, filterkeys.file_name, filterkeys.activity_mtd_usd, filterkeys.settle_date_opening_bal, filterkeys.closing_balance_usd, filterkeys.trade_date_closing_bal, filterkeys.raw_portfolio, filterkeys.closing_balance_local, filterkeys.activity_mtd_local, filterkeys.opening_balance_mtd_usd,
  • filterkeys.a_knowlegde_begin_date, filterkeys.a_knowledge_end_date, filterkeys.b_knowlegde_begin_date, filterkeys.b_knowledge_end_date, filterkeys.c_knowlegde_begin_date, filterkeys.c_knowledge_end_date
  • Hash Cond: ((milestone.legal_entity_family_id = filterkeys.f_legal_entity_family_id) AND (milestone.dataset_type_id = filterkeys.f_dataset_type_id) AND (milestone.dataset_level_id = filterkeys.f_dataset_level_id) AND (milestone.dataset_source_id = filterkeys.f_dataset_source_id) AND (milestone.effective_begin_date = filterkeys.f_effective_begin_date))
  • Join Filter: ((filterkeys.a_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.a_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.b_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.b_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.c_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.c_knowledge_end_date > milestone.knowledge_begin_date))
  • Rows Removed by Join Filter: 8,321,881
  • Buffers: shared hit=12,777,276 read=10,922, temp read=758,320 written=1,125,876
  • I/O Timings: read=150.914
  • a.file_name, a.knowledge_begin_date, a.knowledge_end_date, a.effective_begin_date, b.id, b.trade_date_opening_bal, b.settle_date_closing_bal, b.settle_date_opening_bal, b.trade_date_closing_bal, b.knowledge_begin_date, b.knowledge_end_date, b.raw_record_id, b.parser_id
2.          

CTE filtered_data_txns_for_milestones

3. 23,906.586 47,468.019 ↓ 8,412,066.0 8,412,066 1

Nested Loop (cost=3.06..79,966.42 rows=1 width=771) (actual time=46.712..47,468.019 rows=8,412,066 loops=1)

  • Output: 1, a.id, b.id, a.id, a.parser_name, a.parser_id, a.raw_record_id, filterkeys_1.legal_entity_id, filterkeys_1.legal_entity_family_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.strategy_id, a.legal_entity_family_id, a.legal_entity_id, a.gbo_type_id, a.admin_pm, a.book_id, a.file_id, (c.processed_row ->> 'account_group_category'::text), (c.processed_row ->> 'account_type'::text), a.strategy_id, a.demux_group_id, (c.processed_row ->> 'financial_account'::text), a.currency, a.admin_account, a.custodian_account_id, (c.processed_row ->> 'gbo_type_group'::text), b.trade_date_opening_bal, ((c.processed_row ->> 'opening_balance_mtd_local'::text))::numeric, b.settle_date_closing_bal, a.file_name, ((c.processed_row ->> 'activity_mtd_usd'::text))::numeric, b.settle_date_opening_bal, ((c.processed_row ->> 'closing_balance_usd'::text))::numeric, b.trade_date_closing_bal, (c.processed_row ->> 'raw_portfolio'::text), ((c.processed_row ->> 'closing_balance_local'::text))::numeric, ((c.processed_row ->> 'activity_mtd_local'::text))::numeric, ((c.processed_row ->> 'opening_balance_mtd_usd'::text))::numeric, a.knowledge_begin_date, a.knowledge_end_date, b.knowledge_begin_date, b.knowledge_end_date, c.knowledge_begin_date, c.knowledge_end_date
  • Buffers: shared hit=12,777,275 read=10,922
  • I/O Timings: read=150.914
4. 593.080 7,547.085 ↓ 889,686.0 1,779,372 1

Nested Loop (cost=3.06..79,963.79 rows=2 width=407) (actual time=46.637..7,547.085 rows=1,779,372 loops=1)

  • Output: filterkeys_1.legal_entity_id, filterkeys_1.legal_entity_family_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.strategy_id, a.id, a.parser_name, a.parser_id, a.raw_record_id, a.legal_entity_family_id, a.legal_entity_id, a.gbo_type_id, a.admin_pm, a.book_id, a.file_id, a.strategy_id, a.demux_group_id, a.currency, a.admin_account, a.custodian_account_id,
  • Buffers: shared hit=2,293,507 read=10,922
  • I/O Timings: read=150.914
5. 407.700 2,138.777 ↓ 3,262.3 401,269 1

Hash Join (cost=3.06..79,880.79 rows=123 width=251) (actual time=46.583..2,138.777 rows=401,269 loops=1)

  • Output: filterkeys_1.legal_entity_id, filterkeys_1.legal_entity_family_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.strategy_id, a.id, a.parser_name, a.parser_id, a.raw_record_id, a.legal_entity_family_id, a.legal_entity_id, a.gbo_type_id, a.admin_pm, a.book_id, a.file_id, a.strategy_id, a.demux_group_id, a.currency, a.admin_account, a.custodian_account_id, a.file_name, a.knowledge_begin_date, a.knowledge_end_date, a.effective_begin_date
  • Hash Cond: ((a.legal_entity_id = filterkeys_1.legal_entity_id) AND (a.legal_entity_family_id = filterkeys_1.legal_entity_family_id) AND (a.dataset_level_id = filterkeys_1.dataset_level_id) AND (a.dataset_type_id = filterkeys_1.dataset_type_id) AND (a.strategy_id = filterkeys_1.strategy_id))
  • Buffers: shared hit=32,654 read=10,922
  • I/O Timings: read=150.914
6. 61.035 1,731.023 ↑ 1.0 458,775 1

Append (cost=0.00..68,362.72 rows=460,551 width=235) (actual time=46.488..1,731.023 rows=458,775 loops=1)

  • Buffers: shared hit=32,653 read=10,922
  • I/O Timings: read=150.914
7. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on coffee.processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=1,684) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: a.id, a.parser_name, a.parser_id, a.raw_record_id, a.legal_entity_family_id, a.legal_entity_id, a.gbo_type_id, a.admin_pm, a.book_id, a.file_id, a.strategy_id, a.demux_group_id, a.currency, a.admin_account, a.custodian_account_id, a.file_name, a.knowledge_begin_date, a.knowledge_end_date, a.dataset_level_id, a.dataset_type_id, a.dataset_source_id, a.effective_begin_date
  • Filter: ((a.dataset_source_id = 2) AND (a.effective_begin_date = '2019-12-31'::date) AND (a.available = 'Y'::bpchar) AND ((a.bucket_type)::text = 'REGULAR'::text))
8. 1,669.988 1,669.988 ↑ 1.0 458,775 1

Seq Scan on coffee.processed_feed_data_keys_2019_12_2 a_1 (cost=0.00..68,362.72 rows=460,550 width=235) (actual time=46.487..1,669.988 rows=458,775 loops=1)

  • Output: a_1.id, a_1.parser_name, a_1.parser_id, a_1.raw_record_id, a_1.legal_entity_family_id, a_1.legal_entity_id, a_1.gbo_type_id, a_1.admin_pm, a_1.book_id, a_1.file_id, a_1.strategy_id, a_1.demux_group_id, a_1.currency, a_1.admin_account, a_1.custodian_account_id, a_1.file_name, a_1.knowledge_begin_date, a_1.knowledge_end_date, a_1.dataset_level_id, a_1.dataset_type_id, a_1.dataset_source_id, a_1.effective_begin_date
  • Filter: ((a_1.dataset_source_id = 2) AND (a_1.effective_begin_date = '2019-12-31'::date) AND (a_1.available = 'Y'::bpchar) AND ((a_1.bucket_type)::text = 'REGULAR'::text))
  • Rows Removed by Filter: 340,096
  • Buffers: shared hit=32,653 read=10,922
  • I/O Timings: read=150.914
9. 0.022 0.054 ↑ 1.0 55 1

Hash (cost=1.82..1.82 rows=55 width=28) (actual time=0.054..0.054 rows=55 loops=1)

  • Output: filterkeys_1.legal_entity_id, filterkeys_1.legal_entity_family_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.strategy_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
10. 0.032 0.032 ↑ 1.0 55 1

Seq Scan on sandbox.s92coffee_processed_feed_data_staging_template1580900161744 filterkeys_1 (cost=0.00..1.82 rows=55 width=28) (actual time=0.013..0.032 rows=55 loops=1)

  • Output: filterkeys_1.legal_entity_id, filterkeys_1.legal_entity_family_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.strategy_id
  • Filter: ((filterkeys_1.dataset_source_id = 2) AND (filterkeys_1.effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=1
11. 401.269 4,815.228 ↓ 2.0 4 401,269

Append (cost=0.00..0.65 rows=2 width=160) (actual time=0.006..0.012 rows=4 loops=401,269)

  • Buffers: shared hit=2,260,853
12. 0.000 0.000 ↓ 0.0 0 401,269

Seq Scan on coffee.processed_feed_data b (cost=0.00..0.00 rows=1 width=160) (actual time=0.000..0.000 rows=0 loops=401,269)

  • Output: b.id, b.trade_date_opening_bal, b.settle_date_closing_bal, b.settle_date_opening_bal, b.trade_date_closing_bal, b.knowledge_begin_date, b.knowledge_end_date, b.raw_record_id, b.effective_begin_date, b.parser_id
  • Filter: ((b.dataset_source_id = 2) AND (b.effective_begin_date = '2019-12-31'::date) AND (b.available = 'Y'::bpchar) AND (a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
13. 4,413.959 4,413.959 ↓ 4.0 4 401,269

Index Scan using idx_feed_data_rwid_2019_12_2 on coffee.processed_feed_data_2019_12_2 b_1 (cost=0.42..0.65 rows=1 width=160) (actual time=0.005..0.011 rows=4 loops=401,269)

  • Output: b_1.id, b_1.trade_date_opening_bal, b_1.settle_date_closing_bal, b_1.settle_date_opening_bal, b_1.trade_date_closing_bal, b_1.knowledge_begin_date, b_1.knowledge_end_date, b_1.raw_record_id, b_1.effective_begin_date, b_1.parser_id
  • Index Cond: (b_1.raw_record_id = a.raw_record_id)
  • Filter: ((b_1.dataset_source_id = 2) AND (b_1.effective_begin_date = '2019-12-31'::date) AND (b_1.available = 'Y'::bpchar) AND (a.parser_id = b_1.parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2,260,853
14. 1,779.372 16,014.348 ↓ 2.5 5 1,779,372

Append (cost=0.00..1.27 rows=2 width=1,178) (actual time=0.005..0.009 rows=5 loops=1,779,372)

  • Buffers: shared hit=10,483,768
15. 0.000 0.000 ↓ 0.0 0 1,779,372

Seq Scan on coffee.processed_feed_records c (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=1,779,372)

  • Output: c.processed_row, c.knowledge_begin_date, c.knowledge_end_date, c.raw_record_id, c.effective_begin_date, c.parser_id
  • Filter: ((c.dataset_source_id = 2) AND (c.effective_begin_date = '2019-12-31'::date) AND (c.available = 'Y'::bpchar) AND (a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
16. 14,234.976 14,234.976 ↓ 5.0 5 1,779,372

Index Scan using idx_records_rwid_2019_12_2 on coffee.processed_feed_records_2019_12_2 c_1 (cost=0.42..1.27 rows=1 width=1,178) (actual time=0.004..0.008 rows=5 loops=1,779,372)

  • Output: c_1.processed_row, c_1.knowledge_begin_date, c_1.knowledge_end_date, c_1.raw_record_id, c_1.effective_begin_date, c_1.parser_id
  • Index Cond: (c_1.raw_record_id = a.raw_record_id)
  • Filter: ((c_1.dataset_source_id = 2) AND (c_1.effective_begin_date = '2019-12-31'::date) AND (c_1.available = 'Y'::bpchar) AND (a.parser_id = c_1.parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=10,483,768
17.          

CTE union_parser_mode

18. 0.000 0.007 ↓ 0.0 0 1

Append (cost=12.49..26.69 rows=86 width=17) (actual time=0.007..0.007 rows=0 loops=1)

19. 0.002 0.005 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: etl_to_cocoa_mapping.etl_parser_id, etl_to_cocoa_mapping.in_cocoa_mode, etl_to_cocoa_mapping.etl_dataset_type_id, etl_to_cocoa_mapping.effective_begin_date, etl_to_cocoa_mapping.effective_end_date
  • Group Key: etl_to_cocoa_mapping.etl_parser_id, etl_to_cocoa_mapping.in_cocoa_mode, etl_to_cocoa_mapping.etl_dataset_type_id, etl_to_cocoa_mapping.effective_begin_date, etl_to_cocoa_mapping.effective_end_date
20. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on coffee.etl_to_cocoa_mapping (cost=0.00..11.95 rows=43 width=17) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: etl_to_cocoa_mapping.id, etl_to_cocoa_mapping.etl_file_alias, etl_to_cocoa_mapping.etl_parser_id, etl_to_cocoa_mapping.cocoa_parser_id, etl_to_cocoa_mapping.in_cocoa_mode, etl_to_cocoa_mapping.effective_begin_date, etl_to_cocoa_mapping.effective_end_date, etl_to_cocoa_mapping.knowledge_begin_date, etl_to_cocoa_mapping.knowledge_end_date, etl_to_cocoa_mapping.etl_dataset_type_id
  • Filter: (etl_to_cocoa_mapping.knowledge_end_date > now())
21. 0.002 0.002 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: etl_to_cocoa_mapping_1.cocoa_parser_id, etl_to_cocoa_mapping_1.in_cocoa_mode, etl_to_cocoa_mapping_1.etl_dataset_type_id, etl_to_cocoa_mapping_1.effective_begin_date, etl_to_cocoa_mapping_1.effective_end_date
  • Group Key: etl_to_cocoa_mapping_1.cocoa_parser_id, etl_to_cocoa_mapping_1.in_cocoa_mode, etl_to_cocoa_mapping_1.etl_dataset_type_id, etl_to_cocoa_mapping_1.effective_begin_date, etl_to_cocoa_mapping_1.effective_end_date
22. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on coffee.etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..11.95 rows=43 width=17) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: etl_to_cocoa_mapping_1.id, etl_to_cocoa_mapping_1.etl_file_alias, etl_to_cocoa_mapping_1.etl_parser_id, etl_to_cocoa_mapping_1.cocoa_parser_id, etl_to_cocoa_mapping_1.in_cocoa_mode, etl_to_cocoa_mapping_1.effective_begin_date, etl_to_cocoa_mapping_1.effective_end_date, etl_to_cocoa_mapping_1.knowledge_begin_date, etl_to_cocoa_mapping_1.knowledge_end_date, etl_to_cocoa_mapping_1.etl_dataset_type_id
  • Filter: (etl_to_cocoa_mapping_1.knowledge_end_date > now())
23. 0.017 0.017 ↑ 1.0 61 1

Seq Scan on sandbox.s79coffee_parser_milestone1581431795876 milestone (cost=0.00..1.61 rows=61 width=28) (actual time=0.005..0.017 rows=61 loops=1)

  • Output: milestone.legal_entity_family_id, milestone.dataset_type_id, milestone.dataset_level_id, milestone.dataset_source_id, milestone.effective_begin_date, milestone.knowledge_begin_date
  • Buffers: shared hit=1
24. 16,781.760 95,061.837 ↓ 8,412,066.0 8,412,066 1

Hash (cost=2.42..2.42 rows=1 width=2,220) (actual time=95,061.837..95,061.837 rows=8,412,066 loops=1)

  • Output: filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.legal_entity_family_id, filterkeys.legal_entity_id, filterkeys.gbo_type_id, filterkeys.admin_pm, filterkeys.book_id, filterkeys.file_id, filterkeys.account_group_category, filterkeys.account_type, filterkeys.strategy_id, filterkeys.demux_group_id, filterkeys.financial_account, filterkeys.currency, filterkeys.admin_account, filterkeys.custodian_account_id, filterkeys.gbo_type_group, filterkeys.trade_date_opening_bal, filterkeys.opening_balance_mtd_local, filterkeys.settle_date_closing_bal, filterkeys.file_name, filterkeys.activity_mtd_usd, filterkeys.settle_date_opening_bal, filterkeys.closing_balance_usd, filterkeys.trade_date_closing_bal, filterkeys.raw_portfolio, filterkeys.closing_balance_local, filterkeys.activity_mtd_local, filterkeys.opening_balance_mtd_usd, filterkeys.a_knowlegde_begin_date, filterkeys.a_knowledge_end_date, filterkeys.b_knowlegde_begin_date, filterkeys.b_knowledge_end_date, filterkeys.c_knowlegde_begin_date, filterkeys.c_knowledge_end_date
  • Buckets: 16,384 (originally 1024) Batches: 32 (originally 1) Memory Usage: 1,524,935kB
  • Buffers: shared hit=12,777,275 read=10,922, temp read=369,663 written=1,106,917
  • I/O Timings: read=150.914
25. 6,887.014 78,280.077 ↓ 8,412,066.0 8,412,066 1

Hash Right Join (cost=0.04..2.42 rows=1 width=2,220) (actual time=74,446.946..78,280.077 rows=8,412,066 loops=1)

  • Output: filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.legal_entity_family_id, filterkeys.legal_entity_id, filterkeys.gbo_type_id, filterkeys.admin_pm, filterkeys.book_id, filterkeys.file_id, filterkeys.account_group_category, filterkeys.account_type, filterkeys.strategy_id, filterkeys.demux_group_id, filterkeys.financial_account, filterkeys.currency, filterkeys.admin_account, filterkeys.custodian_account_id, filterkeys.gbo_type_group, filterkeys.trade_date_opening_bal, filterkeys.opening_balance_mtd_local, filterkeys.settle_date_closing_bal, filterkeys.file_name, filterkeys.activity_mtd_usd, filterkeys.settle_date_opening_bal, filterkeys.closing_balance_usd, filterkeys.trade_date_closing_bal, filterkeys.raw_portfolio, filterkeys.closing_balance_local, filterkeys.activity_mtd_local, filterkeys.opening_balance_mtd_usd, filterkeys.a_knowlegde_begin_date, filterkeys.a_knowledge_end_date, filterkeys.b_knowlegde_begin_date, filterkeys.b_knowledge_end_date, filterkeys.c_knowlegde_begin_date, filterkeys.c_knowledge_end_date
  • Hash Cond: ((d.parser_id = filterkeys.parser_id) AND (d.etl_dataset_type_id = filterkeys.f_dataset_type_id))
  • Join Filter: ((d.effective_begin_date <= filterkeys.f_effective_begin_date) AND (d.effective_end_date > filterkeys.f_effective_begin_date))
  • Filter: ((d.parser_id IS NULL) OR ((d.parser_id > 0) AND d.in_cocoa_mode) OR ((d.parser_id < 0) AND (NOT d.in_cocoa_mode)))
  • Buffers: shared hit=12,777,275 read=10,922, temp read=369,663 written=737,269
  • I/O Timings: read=150.914
26. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on union_parser_mode d (cost=0.00..1.72 rows=86 width=17) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: d.parser_id, d.in_cocoa_mode, d.etl_dataset_type_id, d.effective_begin_date, d.effective_end_date
27. 8,023.139 71,393.054 ↓ 8,412,066.0 8,412,066 1

Hash (cost=0.02..0.02 rows=1 width=2,220) (actual time=71,393.053..71,393.054 rows=8,412,066 loops=1)

  • Output: filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.legal_entity_family_id, filterkeys.legal_entity_id, filterkeys.gbo_type_id, filterkeys.admin_pm, filterkeys.book_id, filterkeys.file_id, filterkeys.account_group_category, filterkeys.account_type, filterkeys.strategy_id, filterkeys.demux_group_id, filterkeys.financial_account, filterkeys.currency, filterkeys.admin_account, filterkeys.custodian_account_id, filterkeys.gbo_type_group, filterkeys.trade_date_opening_bal, filterkeys.opening_balance_mtd_local, filterkeys.settle_date_closing_bal, filterkeys.file_name, filterkeys.activity_mtd_usd, filterkeys.settle_date_opening_bal, filterkeys.closing_balance_usd, filterkeys.trade_date_closing_bal, filterkeys.raw_portfolio, filterkeys.closing_balance_local, filterkeys.activity_mtd_local, filterkeys.opening_balance_mtd_usd, filterkeys.a_knowlegde_begin_date, filterkeys.a_knowledge_end_date, filterkeys.b_knowlegde_begin_date, filterkeys.b_knowledge_end_date, filterkeys.c_knowlegde_begin_date, filterkeys.c_knowledge_end_date
  • Buckets: 16,384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,055,873kB
  • Buffers: shared hit=12,777,275 read=10,922, temp written=737,268
  • I/O Timings: read=150.914
28. 63,369.915 63,369.915 ↓ 8,412,066.0 8,412,066 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=2,220) (actual time=46.722..63,369.915 rows=8,412,066 loops=1)

  • Output: filterkeys.f_legal_entity_id, filterkeys.f_legal_entity_family_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_strategy_id, filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.legal_entity_family_id, filterkeys.legal_entity_id, filterkeys.gbo_type_id, filterkeys.admin_pm, filterkeys.book_id, filterkeys.file_id, filterkeys.account_group_category, filterkeys.account_type, filterkeys.strategy_id, filterkeys.demux_group_id, filterkeys.financial_account, filterkeys.currency, filterkeys.admin_account, filterkeys.custodian_account_id, filterkeys.gbo_type_group, filterkeys.trade_date_opening_bal, filterkeys.opening_balance_mtd_local, filterkeys.settle_date_closing_bal, filterkeys.file_name, filterkeys.activity_mtd_usd, filterkeys.settle_date_opening_bal, filterkeys.closing_balance_usd, filterkeys.trade_date_closing_bal, filterkeys.raw_portfolio, filterkeys.closing_balance_local, filterkeys.activity_mtd_local, filterkeys.opening_balance_mtd_usd, filterkeys.a_knowlegde_begin_date, filterkeys.a_knowledge_end_date, filterkeys.b_knowlegde_begin_date, filterkeys.b_knowledge_end_date, filterkeys.c_knowlegde_begin_date, filterkeys.c_knowledge_end_date
  • Buffers: shared hit=12,777,275 read=10,922, temp written=367,607
  • I/O Timings: read=150.914
Planning time : 30.948 ms
Execution time : 103,490.461 ms