explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aM5r

Settings
# exclusive inclusive rows x rows loops node
1. 153.301 2,306.426 ↓ 223.0 223 1

Unique (cost=20,779.44..20,779.47 rows=1 width=103) (actual time=2,032.411..2,306.426 rows=223 loops=1)

2. 1,047.648 2,153.125 ↓ 141,502.0 141,502 1

Sort (cost=20,779.44..20,779.44 rows=1 width=103) (actual time=2,032.410..2,153.125 rows=141,502 loops=1)

  • Sort Key: tc.date, sm.news_type, s.name, tc.reference, sc.country, sc.isocountry2, tc.previous, tc.actual, tc.teforecast, tc.forecast, st.name
  • Sort Method: external merge Disk: 15064kB
3. 74.798 1,105.477 ↓ 141,502.0 141,502 1

Nested Loop (cost=13,261.18..20,779.43 rows=1 width=103) (actual time=232.073..1,105.477 rows=141,502 loops=1)

  • Join Filter: (tc.country_id = sc.countryid)
4. 83.141 606.173 ↓ 20,214.6 141,502 1

Hash Join (cost=13,260.91..20,777.30 rows=7 width=98) (actual time=232.052..606.173 rows=141,502 loops=1)

  • Hash Cond: (tc.category_id = st.id)
5. 287.675 522.927 ↓ 2,948.0 141,502 1

Hash Join (cost=13,253.98..20,769.71 rows=48 width=86) (actual time=231.925..522.927 rows=141,502 loops=1)

  • Hash Cond: ((tc.category_id = sm.category_id) AND (tc.event_id = s.id) AND (tc.country_id = sm.country_id))
  • Join Filter: (((sm.stock_id = 61) AND (((sm.abnormal_return_1_day > 0.013) AND (sm.abnormal_return_1_day_rank > '70'::numeric)) OR ((sm.abnormal_return_2_day > 0.018) AND (sm.abnormal_return_2_day_rank > '70'::numeric)) OR ((sm.abnormal_return_5_day > 0.035) AND (sm.abnormal_return_5_day_rank > '70'::numeric)) OR (sm.abnormal_return_avg_rank_gic > '70'::numeric) OR (sm.abnormal_return_avg_rank > '70'::numeric))) OR (tc.category_id = ANY ('{270,272,273,274,154,156,285,34,36,167,46,47,49,64,65,66,68,72,201,200,75,80,84,103,107,112,121}'::integer[])))
  • Rows Removed by Join Filter: 42280
6. 5.083 6.339 ↑ 1.0 6,083 1

Bitmap Heap Scan on screening_tecalendar tc (cost=765.45..5,033.76 rows=6,327 width=38) (actual time=1.337..6.339 rows=6,083 loops=1)

  • Recheck Cond: (date >= '2020-02-10 00:00:00+00'::timestamp with time zone)
  • Heap Blocks: exact=576
7. 1.256 1.256 ↑ 1.0 6,083 1

Bitmap Index Scan on screening_tecalendar_date_3fe0e2a9 (cost=0.00..763.87 rows=6,327 width=0) (actual time=1.256..1.256 rows=6,083 loops=1)

  • Index Cond: (date >= '2020-02-10 00:00:00+00'::timestamp with time zone)
8. 116.895 228.913 ↑ 1.3 96,145 1

Hash (cost=7,916.44..7,916.44 rows=126,805 width=122) (actual time=228.912..228.913 rows=96,145 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 2827kB
9. 92.660 112.018 ↑ 1.3 96,145 1

Hash Join (cost=138.82..7,916.44 rows=126,805 width=122) (actual time=1.603..112.018 rows=96,145 loops=1)

  • Hash Cond: (sm.event_id = s.id)
10. 17.791 17.791 ↑ 1.3 96,145 1

Seq Scan on screening_macroeventstockrelation sm (cost=0.00..6,034.05 rows=126,805 width=95) (actual time=0.005..17.791 rows=96,145 loops=1)

11. 0.896 1.567 ↑ 1.3 3,147 1

Hash (cost=87.81..87.81 rows=4,081 width=27) (actual time=1.567..1.567 rows=3,147 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 221kB
12. 0.671 0.671 ↑ 1.3 3,147 1

Seq Scan on screening_tecalendarevent s (cost=0.00..87.81 rows=4,081 width=27) (actual time=0.003..0.671 rows=3,147 loops=1)

13. 0.070 0.105 ↑ 1.0 219 1

Hash (cost=4.19..4.19 rows=219 width=24) (actual time=0.105..0.105 rows=219 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
14. 0.035 0.035 ↑ 1.0 219 1

Seq Scan on screening_tecalendarcategory st (cost=0.00..4.19 rows=219 width=24) (actual time=0.003..0.035 rows=219 loops=1)

15. 424.506 424.506 ↑ 1.0 1 141,502

Index Scan using screening_country_pkey on screening_country sc (cost=0.27..0.29 rows=1 width=17) (actual time=0.002..0.003 rows=1 loops=141,502)

  • Index Cond: (countryid = sm.country_id)
Planning time : 6.235 ms
Execution time : 2,311.896 ms