explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5de

Settings
# exclusive inclusive rows x rows loops node
1. 24.602 94.825 ↓ 10,060.0 10,060 1

Nested Loop (cost=77.34..106.75 rows=1 width=215) (actual time=13.348..94.825 rows=10,060 loops=1)

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

CTE union_parser_mode

3. 0.065 0.746 ↓ 1.1 792 1

Append (cost=23.27..61.62 rows=754 width=17) (actual time=0.296..0.746 rows=792 loops=1)

4. 0.207 0.359 ↑ 1.1 474 1

HashAggregate (cost=23.27..28.35 rows=508 width=17) (actual time=0.295..0.359 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.152 0.152 ↑ 1.1 476 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..16.92 rows=508 width=17) (actual time=0.012..0.152 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
6. 0.208 0.322 ↓ 1.3 318 1

HashAggregate (cost=23.27..25.73 rows=246 width=17) (actual time=0.267..0.322 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.114 0.114 ↑ 1.1 476 1

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

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
8. 8.590 50.103 ↓ 10,060.0 10,060 1

Nested Loop (cost=15.72..40.83 rows=1 width=1,497) (actual time=13.315..50.103 rows=10,060 loops=1)

  • Join Filter: ((c.knowledge_begin_date <= milestone.knowledge_begin_date) AND (c.knowledge_end_date > milestone.knowledge_begin_date))
9. 8.016 21.393 ↓ 10,060.0 10,060 1

Hash Right Join (cost=15.72..36.47 rows=1 width=121) (actual time=13.299..21.393 rows=10,060 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))
  • Rows Removed by Join Filter: 10060
  • 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. 0.950 0.950 ↓ 1.1 792 1

CTE Scan on union_parser_mode d (cost=0.00..15.08 rows=754 width=17) (actual time=0.297..0.950 rows=792 loops=1)

11. 3.798 12.427 ↓ 10,060.0 10,060 1

Hash (cost=15.70..15.70 rows=1 width=121) (actual time=12.427..12.427 rows=10,060 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1740kB
12. 1.382 8.629 ↓ 10,060.0 10,060 1

Nested Loop (cost=0.00..15.70 rows=1 width=121) (actual time=0.037..8.629 rows=10,060 loops=1)

13. 0.017 0.017 ↓ 10.0 10 1

Seq Scan on s121coffee_parser_milestone1522241123986_new milestone (cost=0.00..10.00 rows=1 width=28) (actual time=0.009..0.017 rows=10 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date))
14. 0.760 7.230 ↓ 503.0 1,006 10

Append (cost=0.00..5.68 rows=2 width=109) (actual time=0.011..0.723 rows=1,006 loops=10)

15. 0.000 0.000 ↓ 0.0 0 10

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.knowl
16. 6.470 6.470 ↓ 1,006.0 1,006 10

Index Scan using idx_data_keys_date_book_knw_2019_01_1 on processed_feed_data_keys_2019_01_1 a_1 (cost=0.56..5.68 rows=1 width=109) (actual time=0.010..0.647 rows=1,006 loops=10)

  • Index Cond: ((effective_begin_date = '2019-01-11'::date) AND (dataset_source_id = 1) AND (dataset_type_id = milestone.dataset_type_id) AND (dataset_level_id = milestone.dataset_level_id) AND (book_id
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.knowledge_begin_date))
17. 0.000 20.120 ↑ 2.0 1 10,060

Append (cost=0.00..4.33 rows=2 width=1,396) (actual time=0.002..0.002 rows=1 loops=10,060)

18. 0.000 0.000 ↓ 0.0 0 10,060

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=10,060)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
19. 20.120 20.120 ↑ 1.0 1 10,060

Index Scan using idx_records_rwid_2019_01_1 on processed_feed_records_2019_01_1 c_1 (cost=0.43..4.33 rows=1 width=1,396) (actual time=0.002..0.002 rows=1 loops=10,060)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
20. 0.000 20.120 ↑ 2.0 1 10,060

Append (cost=0.00..4.26 rows=2 width=62) (actual time=0.002..0.002 rows=1 loops=10,060)

21. 0.000 0.000 ↓ 0.0 0 10,060

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=10,060)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar) AND (c.raw_record_id = raw_record_id) AND (c.parser_id = parser_id))
22. 20.120 20.120 ↑ 1.0 1 10,060

Index Scan using idx_feed_data_rwid_2019_01_1 on processed_feed_data_2019_01_1 b_1 (cost=0.43..4.26 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=10,060)

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