explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a7U8

Settings
# exclusive inclusive rows x rows loops node
1. 20.212 1,452.844 ↓ 27,299.0 27,299 1

Nested Loop (cost=24,955.56..24,964.11 rows=1 width=1,610) (actual time=1,391.018..1,452.844 rows=27,299 loops=1)

2.          

CTE filtered_data_txns_for_milestones

3. 371.316 1,282.145 ↓ 27,299.0 27,299 1

GroupAggregate (cost=24,933.66..24,933.88 rows=1 width=955) (actual time=640.516..1,282.145 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, ((a.processed_row ->> 'isin'::text)), a.book_id, a.dataset_level_id, a.currency, a.admin_account, a.knowledge_begin_date, a.knowledge_end_date
4. 826.821 910.829 ↓ 54,249.0 54,249 1

Sort (cost=24,933.66..24,933.67 rows=1 width=1,347) (actual time=640.452..910.829 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, ((a.processed_row ->> 'isin'::text)), a.currency, a.admin_account, a.knowledge_begin_date, a.knowledge_end_date
  • Sort Method: external merge Disk: 60728kB
5. 32.156 84.008 ↓ 54,249.0 54,249 1

Nested Loop (cost=0.58..24,933.65 rows=1 width=1,347) (actual time=0.072..84.008 rows=54,249 loops=1)

6. 0.016 0.016 ↓ 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.011..0.016 rows=4 loops=1)

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

Index Scan using idx_date_book_knw_record on processed_feed_record a (cost=0.43..24,673.84 rows=24,535 width=1,249) (actual time=0.012..12.959 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))
8.          

CTE union_parser_mode

9. 0.015 0.241 ↑ 1.1 150 1

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

10. 0.061 0.137 ↑ 1.0 126 1

HashAggregate (cost=9.12..10.41 rows=129 width=17) (actual time=0.119..0.137 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
11. 0.076 0.076 ↑ 1.0 129 1

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

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
12. 0.048 0.089 ↑ 1.4 24 1

HashAggregate (cost=9.12..9.46 rows=34 width=17) (actual time=0.087..0.089 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
13. 0.041 0.041 ↑ 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.008..0.041 rows=129 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
14. 25.335 1,405.333 ↓ 27,299.0 27,299 1

Hash Right Join (cost=0.04..4.54 rows=1 width=1,540) (actual time=1,390.997..1,405.333 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)))
15. 0.288 0.288 ↑ 1.1 150 1

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

16. 32.580 1,379.710 ↓ 27,299.0 27,299 1

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

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 10434kB
17. 1,347.130 1,347.130 ↓ 27,299.0 27,299 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=1,540) (actual time=640.524..1,347.130 rows=27,299 loops=1)

18. 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.18 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))
  • Heap Fetches: 27299
Planning time : 3.144 ms
Execution time : 1,469.195 ms