explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JaRy : Optimization for: plan #YDBP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 118.974 2,456.676 ↓ 223,090.0 223,090 1

Nested Loop (cost=74,524.50..82,612.35 rows=1 width=198) (actual time=16.246..2,456.676 rows=223,090 loops=1)

2.          

CTE ev

3. 24.087 429.744 ↓ 1.0 341,718 1

Append (cost=0.00..74,516.61 rows=340,503 width=30) (actual time=0.018..429.744 rows=341,718 loops=1)

4. 405.657 405.657 ↓ 1.0 341,718 1

Seq Scan on rpa_event_2019_05 (cost=0.00..72,814.10 rows=340,503 width=30) (actual time=0.018..405.657 rows=341,718 loops=1)

  • Filter: ((event_sentiment_score IS NOT NULL) AND (timestamp_utc >= '2019-05-01 00:00:00+00'::timestamp with time zone) AND (timestamp_utc < '2019-06-01 00:00:00+00'::timestamp with time zone) AND (relevance >= 100) AND ((topic_group)::text = 'earnings'::text))
  • Rows Removed by Filter: 530027
5. 134.477 727.764 ↓ 268,323.0 268,323 1

Hash Join (cost=7.89..8,094.87 rows=1 width=139) (actual time=16.199..727.764 rows=268,323 loops=1)

  • Hash Cond: (ev.rp_source_id = en2.rp_entity_id)
  • Join Filter: ((ev.timestamp_utc >= en2.valid_from) AND (ev.timestamp_utc <= en2.valid_to))
6. 577.146 577.146 ↓ 1.0 341,718 1

CTE Scan on ev (cost=0.00..6,810.06 rows=340,503 width=80) (actual time=0.021..577.146 rows=341,718 loops=1)

7. 0.798 16.141 ↓ 6,139.0 6,139 1

Hash (cost=7.88..7.88 rows=1 width=59) (actual time=16.140..16.141 rows=6,139 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 598kB
8. 15.343 15.343 ↓ 6,139.0 6,139 1

Index Scan using rpa_entity_source_rank_idx on rpa_entity en2 (cost=0.29..7.88 rows=1 width=59) (actual time=0.029..15.343 rows=6,139 loops=1)

  • Filter: ((entity_type = 'SRCE'::bpchar) AND ((data_value)::integer <= 2))
  • Rows Removed by Filter: 14698
9. 1,609.938 1,609.938 ↑ 1.0 1 268,323

Index Scan using rpa_entity_entity_id_idx on rpa_entity en1 (cost=0.00..0.86 rows=1 width=59) (actual time=0.004..0.006 rows=1 loops=268,323)

  • Index Cond: (rp_entity_id = ev.rp_entity_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((entity_type = 'COMP'::bpchar) AND ((data_type)::text = 'CUSIP'::text) AND (ev.timestamp_utc >= valid_from) AND (ev.timestamp_utc <= valid_to))
  • Rows Removed by Filter: 17