explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZDuM

Settings
# exclusive inclusive rows x rows loops node
1. 112.255 1,844,278.913 ↓ 0.0 0 1

Merge Join (cost=503,197.09..503,411.39 rows=1 width=1,642) (actual time=1,844,278.913..1,844,278.913 rows=0 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
  • Rows Removed by Join Filter: 326322
2.          

CTE filtered_data_txns_for_milestones

3. 51,777.366 1,841,755.105 ↓ 2.6 108,774 1

GroupAggregate (cost=475,802.78..485,043.65 rows=41,532 width=988) (actual time=1,660,346.794..1,841,755.105 rows=108,774 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. 989,248.334 1,789,977.739 ↓ 134.5 5,584,560 1

Sort (cost=475,802.78..475,906.61 rows=41,532 width=2,124) (actual time=1,660,345.566..1,789,977.739 rows=5,584,560 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: external merge Disk: 10986744kB
5. 9,264.902 800,729.405 ↓ 134.5 5,584,560 1

Nested Loop (cost=0.00..396,239.87 rows=41,532 width=2,124) (actual time=11.292..800,729.405 rows=5,584,560 loops=1)

6. 1,978.308 685,357.863 ↓ 27,375.3 5,584,560 1

Nested Loop (cost=0.00..395,693.69 rows=204 width=1,936) (actual time=7.056..685,357.863 rows=5,584,560 loops=1)

7. 1,468.531 13,232.355 ↓ 5,584,560.0 5,584,560 1

Nested Loop (cost=0.00..395,690.90 rows=1 width=196) (actual time=4.354..13,232.355 rows=5,584,560 loops=1)

8. 1.400 1.400 ↓ 18.0 18 1

Seq Scan on pk_ytd filterkeys_1 (cost=0.00..22.45 rows=1 width=70) (actual time=1.387..1.400 rows=18 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2018-12-31'::date))
9. 1,114.992 11,762.424 ↓ 3.3 310,253 18

Append (cost=0.00..394,740.21 rows=92,824 width=126) (actual time=3.180..653.468 rows=310,253 loops=18)

10. 0.000 0.000 ↓ 0.0 0 18

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=18)

  • 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. 10,647.432 10,647.432 ↓ 3.3 310,253 18

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..394,740.21 rows=92,823 width=126) (actual time=3.178..591.524 rows=310,253 loops=18)

  • 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))
  • Rows Removed by Filter: 19181
12. 5,584.560 670,147.200 ↑ 2.0 1 5,584,560

Append (cost=0.00..2.77 rows=2 width=1,744) (actual time=0.119..0.120 rows=1 loops=5,584,560)

13. 0.000 0.000 ↓ 0.0 0 5,584,560

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=5,584,560)

  • 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. 664,562.640 664,562.640 ↑ 1.0 1 5,584,560

Index Scan using idx_records_rwid_2018_12_2 on processed_feed_records_2018_12_2 c_1 (cost=0.43..2.77 rows=1 width=1,744) (actual time=0.118..0.119 rows=1 loops=5,584,560)

  • 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. 5,584.560 106,106.640 ↑ 2.0 1 5,584,560

Append (cost=0.00..2.15 rows=2 width=188) (actual time=0.018..0.019 rows=1 loops=5,584,560)

16. 0.000 0.000 ↓ 0.0 0 5,584,560

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=5,584,560)

  • 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. 100,522.080 100,522.080 ↑ 1.0 1 5,584,560

Index Scan using idx_feed_data_rwid_2018_12_2 on processed_feed_data_2018_12_2 b_1 (cost=0.43..2.15 rows=1 width=188) (actual time=0.018..0.018 rows=1 loops=5,584,560)

  • 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.052 0.750 ↓ 1.1 792 1

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

20. 0.250 0.380 ↑ 1.0 474 1

HashAggregate (cost=21.36..26.12 rows=476 width=17) (actual time=0.313..0.380 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.130 0.130 ↑ 1.0 476 1

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

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

HashAggregate (cost=21.36..23.64 rows=228 width=17) (actual time=0.273..0.318 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.130 0.130 ↑ 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.007..0.130 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
24. 1,640.744 1,844,141.996 ↓ 8.5 108,774 1

Sort (cost=17,996.01..18,028.09 rows=12,835 width=1,572) (actual time=1,842,989.851..1,844,141.996 rows=108,774 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: external sort Disk: 134504kB
25. 164.635 1,842,501.252 ↓ 8.5 108,774 1

Hash Left Join (cost=24.64..8,345.66 rows=12,835 width=1,572) (actual time=1,660,348.072..1,842,501.252 rows=108,774 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. 1,842,335.466 1,842,335.466 ↓ 2.6 108,774 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..830.64 rows=41,532 width=1,572) (actual time=1,660,346.801..1,842,335.466 rows=108,774 loops=1)

27. 0.143 1.151 ↓ 1.1 784 1

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

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

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

29. 23.152 24.662 ↓ 150.8 218,699 1

Sort (cost=100.64..104.26 rows=1,450 width=28) (actual time=1.645..24.662 rows=218,699 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. 1.510 1.510 ↑ 161.1 9 1

Seq Scan on pk_ytd_milestone milestone (cost=0.00..24.50 rows=1,450 width=28) (actual time=1.508..1.510 rows=9 loops=1)