explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YnFr

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 2.138 ↓ 15.0 15 1

Merge Join (cost=228,601.82..228,681.71 rows=1 width=1,642) (actual time=2.124..2.138 rows=15 loops=1)

  • Merge Cond: ((filterkeys.f_dataset_type_id = milestone.dataset_type_id) AND (filterkeys.f_dataset_level_id = milestone.dataset_level_id) AND (filterkeys.f_dataset_source_id = milestone.dataset_source_id) AND (filterkeys.f_effective_beg
  • 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 (f
2.          

CTE filtered_data_txns_for_milestones

3. 0.305 0.936 ↑ 2,507.4 5 1

GroupAggregate (cost=220,238.06..223,027.54 rows=12,537 width=988) (actual time=0.726..0.936 rows=5 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.dat
4. 0.258 0.631 ↑ 321.5 39 1

Sort (cost=220,238.06..220,269.40 rows=12,537 width=2,169) (actual time=0.616..0.631 rows=39 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.
  • Sort Method: quicksort Memory: 103kB
5. 0.024 0.373 ↑ 321.5 39 1

Nested Loop (cost=0.00..207,600.17 rows=12,537 width=2,169) (actual time=0.064..0.373 rows=39 loops=1)

6. 0.014 0.232 ↑ 2.9 39 1

Nested Loop (cost=0.00..207,501.78 rows=112 width=1,980) (actual time=0.050..0.232 rows=39 loops=1)

7. 0.007 0.101 ↓ 39.0 39 1

Nested Loop (cost=0.00..207,500.32 rows=1 width=196) (actual time=0.038..0.101 rows=39 loops=1)

8. 0.010 0.010 ↓ 6.0 6 1

Seq Scan on pk_20181231_s21711coffee_processed_feed_data_staging_template15 filterkeys_1 (cost=0.00..22.45 rows=1 width=70) (actual time=0.009..0.010 rows=6 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date))
9. 0.006 0.084 ↑ 11,661.0 6 6

Append (cost=0.00..206,778.21 rows=69,966 width=126) (actual time=0.007..0.014 rows=6 loops=6)

10. 0.000 0.000 ↓ 0.0 0 6

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

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.dataset_level_id =
11. 0.078 0.078 ↑ 11,660.8 6 6

Index Scan using idx_data_keys_date_book_knw_2018_12_2 on processed_feed_data_keys_2018_12_2 a_1 (cost=0.56..206,778.21 rows=69,965 width=126) (actual time=0.007..0.013 rows=6 loops=6)

  • Index Cond: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (dataset_type_id = filterkeys_1.dataset_type_id) AND (dataset_level_id = filterkeys_1.dataset_level_id
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND ((filterkeys_1.accounting_period)::text = (accounting_period)::text))
12. 0.000 0.117 ↑ 2.0 1 39

Append (cost=0.00..1.45 rows=2 width=1,788) (actual time=0.003..0.003 rows=1 loops=39)

13. 0.000 0.000 ↓ 0.0 0 39

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

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
14. 0.117 0.117 ↑ 1.0 1 39

Index Scan using idx_records_rwid_2018_12_2 on processed_feed_records_2018_12_2 c_1 (cost=0.43..1.45 rows=1 width=1,788) (actual time=0.002..0.003 rows=1 loops=39)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
15. 0.039 0.117 ↑ 2.0 1 39

Append (cost=0.00..0.58 rows=2 width=189) (actual time=0.003..0.003 rows=1 loops=39)

16. 0.000 0.000 ↓ 0.0 0 39

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=344) (actual time=0.000..0.000 rows=0 loops=39)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
17. 0.078 0.078 ↑ 1.0 1 39

Index Scan using idx_feed_data_rwid_2018_12_2 on processed_feed_data_2018_12_2 b_1 (cost=0.43..0.58 rows=1 width=189) (actual time=0.002..0.002 rows=1 loops=39)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
18.          

CTE union_parser_mode

19. 0.057 0.717 ↓ 1.1 792 1

Append (cost=21.36..56.80 rows=704 width=17) (actual time=0.274..0.717 rows=792 loops=1)

20. 0.216 0.352 ↑ 1.0 474 1

HashAggregate (cost=21.36..26.12 rows=476 width=17) (actual time=0.273..0.352 rows=474 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
21. 0.136 0.136 ↑ 1.0 476 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..15.41 rows=476 width=17) (actual time=0.011..0.136 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
22. 0.196 0.308 ↓ 1.4 318 1

HashAggregate (cost=21.36..23.64 rows=228 width=17) (actual time=0.260..0.308 rows=318 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
23. 0.112 0.112 ↑ 1.0 476 1

Seq Scan on etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..15.41 rows=476 width=17) (actual time=0.005..0.112 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
24. 0.016 2.103 ↑ 774.8 5 1

Sort (cost=5,416.84..5,426.53 rows=3,874 width=1,572) (actual time=2.101..2.103 rows=5 loops=1)

  • Sort Key: filterkeys.f_dataset_type_id, filterkeys.f_dataset_level_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_book_id
  • Sort Method: quicksort Memory: 28kB
25. 0.013 2.087 ↑ 774.8 5 1

Hash Left Join (cost=24.64..2,536.46 rows=3,874 width=1,572) (actual time=1.860..2.087 rows=5 loops=1)

  • Hash Cond: ((filterkeys.parser_id = d.parser_id) AND (filterkeys.f_dataset_type_id = d.etl_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)))
26. 0.953 0.953 ↑ 2,507.4 5 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..250.74 rows=12,537 width=1,572) (actual time=0.731..0.953 rows=5 loops=1)

27. 0.135 1.121 ↓ 1.1 784 1

Hash (cost=14.08..14.08 rows=704 width=17) (actual time=1.121..1.121 rows=784 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
28. 0.986 0.986 ↓ 1.1 792 1

CTE Scan on union_parser_mode d (cost=0.00..14.08 rows=704 width=17) (actual time=0.275..0.986 rows=792 loops=1)

29. 0.012 0.016 ↑ 80.6 18 1

Sort (cost=100.64..104.26 rows=1,450 width=28) (actual time=0.014..0.016 rows=18 loops=1)

  • Sort Key: milestone.dataset_type_id, milestone.dataset_level_id, milestone.dataset_source_id, milestone.effective_begin_date, milestone.book_id
  • Sort Method: quicksort Memory: 25kB
30. 0.004 0.004 ↑ 161.1 9 1

Seq Scan on pk_20181231_s21711coffee_parser_milestone1546512060302 milestone (cost=0.00..24.50 rows=1,450 width=28) (actual time=0.003..0.004 rows=9 loops=1)