explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pPyY

Settings
# exclusive inclusive rows x rows loops node
1. 1,186.958 5,810.305 ↓ 148,370.9 1,780,451 1

Nested Loop (cost=79,956.77..79,967.28 rows=12 width=11,421) (actual time=0.762..5,810.305 rows=1,780,451 loops=1)

2.          

CTE a

3. 297.979 749.369 ↓ 2,931.9 401,667 1

Hash Join (cost=3.66..79,956.77 rows=137 width=4,799) (actual time=0.721..749.369 rows=401,667 loops=1)

  • Hash Cond: ((a_1.legal_entity_id = filterkeys.legal_entity_id) AND (a_1.legal_entity_family_id = filterkeys.legal_entity_family_id) AND (a_1.dataset_level_id = filterkeys.dataset_level_id) AND (a_1.dataset_type_id = filterkeys.dataset_type_id) AND (a_1.strategy_id = filterkeys.strategy_id))
4. 45.863 451.357 ↑ 1.0 458,775 1

Append (cost=0.00..68,433.16 rows=460,743 width=4,799) (actual time=0.674..451.357 rows=458,775 loops=1)

5. 0.023 0.023 ↓ 0.0 0 1

Seq Scan on processed_feed_data_keys a_1 (cost=0.00..0.00 rows=1 width=9,116) (actual time=0.023..0.023 rows=0 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
6. 405.471 405.471 ↑ 1.0 458,775 1

Seq Scan on processed_feed_data_keys_2019_12_2 a_2 (cost=0.00..68,433.16 rows=460,742 width=4,799) (actual time=0.649..405.471 rows=458,775 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Rows Removed by Filter: 340,096
7. 0.016 0.033 ↑ 1.0 71 1

Hash (cost=2.06..2.06 rows=71 width=28) (actual time=0.033..0.033 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
8. 0.017 0.017 ↑ 1.0 71 1

Seq Scan on s83coffee_processed_feed_data_staging_template1581440489943 filterkeys (cost=0.00..2.06 rows=71 width=28) (actual time=0.006..0.017 rows=71 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
9. 1,410.011 1,410.011 ↓ 401,667.0 401,667 1

CTE Scan on a (cost=0.00..3.08 rows=1 width=9,116) (actual time=0.730..1,410.011 rows=401,667 loops=1)

  • Filter: (effective_begin_date = '2019-12-31'::date)
10. 0.000 3,213.336 ↓ 2.0 4 401,667

Append (cost=0.00..7.41 rows=2 width=2,305) (actual time=0.004..0.008 rows=4 loops=401,667)

11. 0.000 0.000 ↓ 0.0 0 401,667

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=3,524) (actual time=0.000..0.000 rows=0 loops=401,667)

  • Filter: ((effective_begin_date = '2019-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
12. 3,213.336 3,213.336 ↓ 4.0 4 401,667

Index Scan using idx_feed_data_rwid_2019_12_2 on processed_feed_data_2019_12_2 b_1 (cost=0.42..7.41 rows=1 width=2,305) (actual time=0.004..0.008 rows=4 loops=401,667)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((effective_begin_date = '2019-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Rows Removed by Filter: 2
Planning time : 16.551 ms
Execution time : 5,922.508 ms