explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NCPX

Settings
# exclusive inclusive rows x rows loops node
1. 25.300 1,833.543 ↓ 27,299.0 27,299 1

Nested Loop (cost=22,535.36..22,543.91 rows=1 width=1,642) (actual time=1,765.224..1,833.543 rows=27,299 loops=1)

2.          

CTE filtered_data_txns_for_milestones

3. 386.365 1,649.134 ↓ 27,299.0 27,299 1

GroupAggregate (cost=22,513.45..22,513.68 rows=1 width=987) (actual time=944.147..1,649.134 rows=27,299 loops=1)

  • Group Key: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, filterkeys_1.accounting_period, a.pnl_spn, a.demux_group_id, a.dataset_source_id, a.gbo_type_id, a.dataset_type_id, a.admin_pm, a.accounting_period, ((c.processed_row ->> 'isin'::text)), a.book_id, a.dataset_level_id, a.currency, a.admin_account, a.knowledge_begin_date, a.knowledge_end_date, b.knowledge_begin_date, b.knowledge_end_date, c.knowledge_begin_date, c.knowledge_end_date
4. 876.284 1,262.769 ↓ 54,249.0 54,249 1

Sort (cost=22,513.45..22,513.46 rows=1 width=1,387) (actual time=944.081..1,262.769 rows=54,249 loops=1)

  • Sort Key: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.book_id, filterkeys_1.accounting_period, a.pnl_spn, a.demux_group_id, a.gbo_type_id, a.admin_pm, ((c.processed_row ->> 'isin'::text)), a.currency, a.admin_account, a.knowledge_begin_date, a.knowledge_end_date, b.knowledge_begin_date, b.knowledge_end_date, c.knowledge_begin_date, c.knowledge_end_date
  • Sort Method: external merge Disk: 62856kB
5. 53.650 386.485 ↓ 54,249.0 54,249 1

Nested Loop (cost=1.44..22,513.44 rows=1 width=1,387) (actual time=0.057..386.485 rows=54,249 loops=1)

6. 51.679 224.337 ↓ 54,249.0 54,249 1

Nested Loop (cost=1.01..22,512.21 rows=1 width=463) (actual time=0.043..224.337 rows=54,249 loops=1)

7. 11.308 64.160 ↓ 54,249.0 54,249 1

Nested Loop (cost=0.58..22,511.61 rows=1 width=201) (actual time=0.032..64.160 rows=54,249 loops=1)

8. 0.008 0.008 ↓ 4.0 4 1

Index Only Scan using admin_coffee_processed_feed_d_dataset_level_id_dataset_type_idx on admin_coffee_processed_feed_data_staging_template_3 filterkeys_1 (cost=0.15..14.46 rows=1 width=70) (actual time=0.006..0.008 rows=4 loops=1)

  • Index Cond: ((dataset_source_id = 2) AND (effective_begin_date = '2019-02-28'::date))
  • Heap Fetches: 4
9. 52.844 52.844 ↑ 1.9 13,562 4

Index Scan using idx_data_keys_date_book_knw_2019_02_2_1 on processed_feed_data_keys_2019_02_2_1 a (cost=0.43..22,242.88 rows=25,427 width=131) (actual time=0.005..13.211 rows=13,562 loops=4)

  • Index Cond: ((effective_begin_date = '2019-02-28'::date) AND (dataset_source_id = 2) AND (dataset_type_id = filterkeys_1.dataset_type_id) AND (dataset_level_id = filterkeys_1.dataset_level_id) AND (book_id = filterkeys_1.book_id))
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND ((filterkeys_1.accounting_period)::text = (accounting_period)::text))
10. 108.498 108.498 ↑ 1.0 1 54,249

Index Scan using idx_feed_data_rwid_2019_02_2_1 on processed_feed_data_2019_02_2_1 b (cost=0.43..0.60 rows=1 width=266) (actual time=0.002..0.002 rows=1 loops=54,249)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-02-28'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
11. 108.498 108.498 ↑ 1.0 1 54,249

Index Scan using idx_records_rwid_2019_02_2_1 on processed_feed_records_2019_02_2_1 c (cost=0.43..1.22 rows=1 width=924) (actual time=0.002..0.002 rows=1 loops=54,249)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-02-28'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
12.          

CTE union_parser_mode

13. 0.002 0.222 ↑ 1.1 150 1

Append (cost=9.12..21.49 rows=163 width=17) (actual time=0.109..0.222 rows=150 loops=1)

14. 0.073 0.129 ↑ 1.0 126 1

HashAggregate (cost=9.12..10.41 rows=129 width=17) (actual time=0.109..0.129 rows=126 loops=1)

  • 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
15. 0.056 0.056 ↑ 1.0 129 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..7.50 rows=129 width=17) (actual time=0.016..0.056 rows=129 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
16. 0.051 0.091 ↑ 1.4 24 1

HashAggregate (cost=9.12..9.46 rows=34 width=17) (actual time=0.086..0.091 rows=24 loops=1)

  • 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
17. 0.040 0.040 ↑ 1.0 129 1

Seq Scan on etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..7.50 rows=129 width=17) (actual time=0.007..0.040 rows=129 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
18. 27.068 1,780.944 ↓ 27,299.0 27,299 1

Hash Right Join (cost=0.04..4.54 rows=1 width=1,572) (actual time=1,765.204..1,780.944 rows=27,299 loops=1)

  • 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)))
19. 0.272 0.272 ↑ 1.1 150 1

CTE Scan on union_parser_mode d (cost=0.00..3.26 rows=163 width=17) (actual time=0.111..0.272 rows=150 loops=1)

20. 35.345 1,753.604 ↓ 27,299.0 27,299 1

Hash (cost=0.02..0.02 rows=1 width=1,572) (actual time=1,753.604..1,753.604 rows=27,299 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 11287kB
21. 1,718.259 1,718.259 ↓ 27,299.0 27,299 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=1,572) (actual time=944.154..1,718.259 rows=27,299 loops=1)

22. 27.299 27.299 ↑ 1.0 1 27,299

Index Only Scan using admin_coffee_parser_milestone_dataset_type_id_book_id_datas_idx on admin_coffee_parser_milestone_3 milestone (cost=0.15..4.20 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=27,299)

  • Index Cond: ((dataset_type_id = filterkeys.f_dataset_type_id) AND (book_id = filterkeys.f_book_id) AND (dataset_level_id = filterkeys.f_dataset_level_id) AND (dataset_source_id = filterkeys.f_dataset_source_id) AND (effective_begin_date = filterkeys.f_effective_begin_date) AND (knowledge_begin_date >= filterkeys.a_knowlegde_begin_date) AND (knowledge_begin_date < filterkeys.a_knowledge_end_date) AND (knowledge_begin_date >= filterkeys.b_knowlegde_begin_date) AND (knowledge_begin_date < filterkeys.b_knowledge_end_date) AND (knowledge_begin_date >= filterkeys.c_knowlegde_begin_date) AND (knowledge_begin_date < filterkeys.c_knowledge_end_date))
  • Heap Fetches: 27299