explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZJmY

Settings
# exclusive inclusive rows x rows loops node
1. 0.902 6.289 ↓ 636.0 636 1

Nested Loop (cost=97.05..133.35 rows=1 width=217) (actual time=2.044..6.289 rows=636 loops=1)

  • Join Filter: ((c.knowledge_begin_date <= milestone.knowledge_begin_date) AND (c.knowledge_end_date > milestone.knowledge_begin_date) AND (a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
2.          

CTE union_parser_mode

3. 0.067 0.778 ↓ 1.1 792 1

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

4. 0.259 0.388 ↑ 1.0 474 1

HashAggregate (cost=21.36..26.12 rows=476 width=17) (actual time=0.296..0.388 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
5. 0.129 0.129 ↑ 1.0 476 1

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

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
6. 0.186 0.323 ↓ 1.4 318 1

HashAggregate (cost=21.36..23.64 rows=228 width=17) (actual time=0.272..0.323 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
7. 0.137 0.137 ↑ 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.008..0.137 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
8. 0.029 4.115 ↓ 636.0 636 1

Nested Loop (cost=40.25..68.09 rows=1 width=165) (actual time=2.021..4.115 rows=636 loops=1)

  • Join Filter: ((b.knowledge_begin_date <= milestone.knowledge_begin_date) AND (b.knowledge_end_date > milestone.knowledge_begin_date))
9. 0.280 2.178 ↓ 636.0 636 1

Hash Right Join (cost=40.25..59.63 rows=1 width=123) (actual time=2.000..2.178 rows=636 loops=1)

  • Hash Cond: ((d.parser_id = a.parser_id) AND (d.etl_dataset_type_id = a.dataset_type_id))
  • Join Filter: ((d.effective_begin_date <= a.effective_begin_date) AND (d.effective_end_date > a.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)))
10. 1.025 1.025 ↓ 1.1 792 1

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

11. 0.224 0.873 ↓ 636.0 636 1

Hash (cost=40.24..40.24 rows=1 width=123) (actual time=0.873..0.873 rows=636 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
12. 0.067 0.649 ↓ 636.0 636 1

Nested Loop (cost=0.00..40.24 rows=1 width=123) (actual time=0.031..0.649 rows=636 loops=1)

13. 0.010 0.010 ↓ 4.0 4 1

Seq Scan on test milestone (cost=0.00..31.75 rows=1 width=28) (actual time=0.009..0.010 rows=4 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date))
14. 0.040 0.572 ↓ 79.5 159 4

Append (cost=0.00..8.46 rows=2 width=111) (actual time=0.011..0.143 rows=159 loops=4)

15. 0.000 0.000 ↓ 0.0 0 4

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=588) (actual time=0.000..0.000 rows=0 loops=4)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.know
16. 0.532 0.532 ↓ 159.0 159 4

Index Scan using idx_data_keys_date_book_knw_2019_01_1 on processed_feed_data_keys_2019_01_1 a_1 (cost=0.43..8.46 rows=1 width=108) (actual time=0.009..0.133 rows=159 loops=4)

  • Index Cond: ((effective_begin_date = '2019-01-09'::date) AND (dataset_source_id = 1) AND (dataset_type_id = milestone.dataset_type_id) AND (dataset_level_id = milestone.dataset_level_id) AND (book_i
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.knowledge_begin_date))
17. 0.636 1.908 ↑ 2.0 1 636

Append (cost=0.00..8.43 rows=2 width=63) (actual time=0.002..0.003 rows=1 loops=636)

18. 0.000 0.000 ↓ 0.0 0 636

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
19. 1.272 1.272 ↑ 1.0 1 636

Index Scan using idx_feed_data_rwid_2019_01_1 on processed_feed_data_2019_01_1 b_1 (cost=0.43..8.43 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=636)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
20. 0.000 1.272 ↑ 2.0 1 636

Append (cost=0.00..8.41 rows=2 width=1,359) (actual time=0.002..0.002 rows=1 loops=636)

21. 0.000 0.000 ↓ 0.0 0 636

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND (b.raw_record_id = raw_record_id) AND (b.parser_id = parser_id))
22. 1.272 1.272 ↑ 1.0 1 636

Index Scan using idx_records_rwid_2019_01_1 on processed_feed_records_2019_01_1 c_1 (cost=0.43..8.41 rows=1 width=1,372) (actual time=0.002..0.002 rows=1 loops=636)

  • Index Cond: (raw_record_id = b.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND (b.parser_id = parser_id))