explain.depesz.com

PostgreSQL's explain analyze made readable

Result: idnv

Settings
# exclusive inclusive rows x rows loops node
1. 4,210.311 22,188.286 ↓ 8,415,073.0 8,415,073 1

Nested Loop (cost=3.66..80,051.73 rows=1 width=8,384) (actual time=0.773..22,188.286 rows=8,415,073 loops=1)

  • Buffers: shared hit=12,807,531
2. 1,091.950 5,514.818 ↓ 890,225.5 1,780,451 1

Nested Loop (cost=3.66..80,049.21 rows=2 width=7,132) (actual time=0.751..5,514.818 rows=1,780,451 loops=1)

  • Buffers: shared hit=2,316,125
3. 353.121 807.865 ↓ 2,931.9 401,667 1

Hash Join (cost=3.66..79,956.77 rows=137 width=4,827) (actual time=0.718..807.865 rows=401,667 loops=1)

  • Hash Cond: ((a.legal_entity_id = filterkeys.legal_entity_id) AND (a.legal_entity_family_id = filterkeys.legal_entity_family_id) AND (a.dataset_level_id = filterkeys.dataset_level_id) AND (a.dataset_type_id = filterkeys.dataset_type_id) AND (a.strategy_id = filterkeys.strategy_id))
  • Buffers: shared hit=52,413
4. 47.130 454.710 ↑ 1.0 458,775 1

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

  • Buffers: shared hit=52,412
5. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=9,116) (actual time=0.002..0.002 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. 407.578 407.578 ↑ 1.0 458,775 1

Seq Scan on processed_feed_data_keys_2019_12_2 a_1 (cost=0.00..68,433.16 rows=460,742 width=4,799) (actual time=0.664..407.578 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
  • Buffers: shared hit=52,412
7. 0.017 0.034 ↑ 1.0 71 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=1
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.007..0.017 rows=71 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=1
9. 401.667 3,615.003 ↓ 2.0 4 401,667

Append (cost=0.00..0.65 rows=2 width=2,305) (actual time=0.005..0.009 rows=4 loops=401,667)

  • Buffers: shared hit=2,263,712
10. 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: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
11. 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..0.65 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: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2,263,712
12. 1,780.451 12,463.157 ↓ 2.5 5 1,780,451

Append (cost=0.00..1.24 rows=2 width=1,252) (actual time=0.004..0.007 rows=5 loops=1,780,451)

  • Buffers: shared hit=10,491,406
13. 0.000 0.000 ↓ 0.0 0 1,780,451

Seq Scan on processed_feed_records c (cost=0.00..0.00 rows=1 width=648) (actual time=0.000..0.000 rows=0 loops=1,780,451)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
14. 10,682.706 10,682.706 ↓ 5.0 5 1,780,451

Index Scan using idx_records_rwid_2019_12_2 on processed_feed_records_2019_12_2 c_1 (cost=0.42..1.24 rows=1 width=1,252) (actual time=0.003..0.006 rows=5 loops=1,780,451)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=10,491,406
Planning time : 26.342 ms
Execution time : 22,589.898 ms