API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 18th, 2017
70
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 13.43 KB | None | 0 0
raw download clone embed print report
  1. EXPLAIN analyze SELECT * FROM ( SELECT DISTINCT ON (ev.eventid) ev.ticker, ev.cname, ev.cscore, ev.cik, ev.cid, ev.mcap, ev.arrowtype, ev.eventid, ev.eventtype, ev.eventtext, ev.eventextra,
  2. ev.eventdata1, ev.eventdata2, ev.eventdate, ev.evcode, ev.eventinfo, ev.insertdate, ev.evname, ev.evpriority, ev.evgroup, ev.displayname, ev.bullish,
  3. COALESCE(wd.buyvalue , 0) AS buyvalue, COALESCE(wd.sellvalue , 0) AS sellvalue, wd.net_ct, wd.ceo_ct, wd.cfo_ct, wd.officer_ct, wd.director_ct,
  4. wd.officer_ct, wd.owner_ct, ev.eventepoch, ev.YEAR, ev.quarter, ev.MONTH, ev.week, ev.eventdatestr, ev.griddatestr, snip.snip_id AS snip_id,
  5. snip.mostnotable, snip.TYPE AS sniptype, split_part(snip.sentiment, ' ', 1) AS snipsentiment, snip.title AS sniptitle,
  6. to_char(snip.publishdate, 'Mon-dd YYYY') AS publishdatestr, age(snip.publishdate) AS snipage,
  7. EXTRACT('epoch' FROM (current_day() - snip.publishdate))::NUMERIC / 86400 AS snipagedays, snip.publishdate - ev.eventdate AS snipeventage,
  8. snip.snippet, COALESCE(CASE WHEN qd.close_price <> 0 THEN ((ev.curquote - qd.close_price) / qd.close_price) * 100 ELSE 0 END, 0) AS gain,
  9. ev.sector FROM ( SELECT ci.ticker, ci.name AS cname, ci.cscore, ci.exportcik AS cik, ci.cid, ci.mcap, ci.industry_id / 100 AS sector, ci.arrowtype, ci.curquote,
  10. ev.eventid, ev.eventtype, ev.eventtext, ev.eventextra, ev.eventdata1, ev.eventdata2, ev.eventdate, ev.evcode, ev.eventinfo, ev.insertdate,
  11. cfg.name AS evname, cfg.priority AS evpriority, cfg.evgroup, cfg.displayname, cfg.bullish, EXTRACT('epoch' FROM ev.eventdate) AS eventepoch,
  12. EXTRACT( YEAR FROM ev.eventdate ) AS YEAR, EXTRACT( quarter FROM ev.eventdate ) AS quarter, EXTRACT( MONTH FROM ev.eventdate ) AS MONTH,
  13. EXTRACT( week FROM ev.eventdate ) AS week, to_char(ev.eventdate, 'Mon-dd') AS eventdatestr, to_char(ev.eventdate, 'Mon-dd YY') AS griddatestr
  14. FROM companyevents ev, companyclassinfo ci, evconfig cfg
  15. WHERE ev.cik = ci.exportcik AND ev.evcode = cfg.evcode AND (( ev.evcode = any(array[305,307,306,308,309])
  16. AND eventExtra = getCurrentBuybackQuarter() )
  17. OR ( ev.evcode = any(array[505,101,104,105,106,107,108,111,103,506,201,204,205,206,208,209,211,212,220,214,216,303,310])
  18. AND eventdate > current_day() - (30 * '1 day'::INTERVAL) ) ) ) ev
  19. LEFT JOIN rnsniplatest snip ON (snip.cik = ev.cik AND snip.publishdate > current_day() - (30* '1 day'::INTERVAL))
  20. LEFT JOIN watchlist_daily_scores wd ON (wd.cik = ev.cik AND wd.period = 30 )
  21. LEFT JOIN quotes qd ON (qd.cid = ev.cid AND qd.trade_date = ev.eventdate AND qd.trade_date >='2004-01-01') ORDER BY ev.eventid ) ss ORDER BY eventdate DESC , evpriority ASC ;
  22.                                                                                                                                                                         QUERY PLAN                            
  23.                                                                                                                                            
  24. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  25. --------------------------------------------------------------------------------------------------------------------------------------------
  26.  Sort  (cost=58127.13..58127.16 ROWS=11 width=798) (actual TIME=717.837..720.171 ROWS=2422 loops=1)
  27.    Sort KEY: ev.eventdate, cfg.priority
  28.    Sort Method:  external MERGE  Disk: 2608kB
  29.    ->  UNIQUE  (cost=58079.43..58126.83 ROWS=11 width=1526) (actual TIME=686.429..690.791 ROWS=2422 loops=1)
  30.          ->  Sort  (cost=58079.43..58103.13 ROWS=9480 width=1526) (actual TIME=686.426..688.964 ROWS=2917 loops=1)
  31.                Sort KEY: ev.eventid
  32.                Sort Method:  external MERGE  Disk: 3168kB
  33.                ->  Nested Loop LEFT JOIN  (cost=1190.55..52062.25 ROWS=9480 width=1526) (actual TIME=199.394..662.030 ROWS=2917 loops=1)
  34.                      JOIN FILTER: ((qd.cid = ci.cid) AND (qd.trade_date = ev.eventdate))
  35.                      ->  Nested Loop LEFT JOIN  (cost=1190.55..50498.02 ROWS=11 width=1518) (actual TIME=199.060..449.464 ROWS=2917 loops=1)
  36.                            ->  Nested Loop LEFT JOIN  (cost=1190.55..50428.82 ROWS=11 width=565) (actual TIME=199.032..432.765 ROWS=2917 loops=1)
  37.                                  ->  Nested Loop  (cost=1190.55..50359.53 ROWS=11 width=244) (actual TIME=198.996..410.609 ROWS=2917 loops=1)
  38.                                        ->  Nested Loop  (cost=1190.55..50296.79 ROWS=7 width=173) (actual TIME=198.739..296.251 ROWS=2426 loops=1)
  39.                                              JOIN FILTER: (ev.evcode = cfg.evcode)
  40.                                              ->  Bitmap Heap Scan ON companyevents ev  (cost=990.30..49771.46 ROWS=12 width=126) (actual TIME=198.142..209.067 ROWS=2426 loops=1)
  41.                                                    Recheck Cond: ((evcode = ANY ('{305,307,306,308,309}'::INTEGER[])) OR ((eventdate > '2011-04-19 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (evcode = ANY ('
  42. {505,101,104,105,106,107,108,111,103,506,201,204,205,206,208,209,211,212,220,214,216,303,310}'::INTEGER[]))))
  43.                                                    FILTER: (((evcode = ANY ('{305,307,306,308,309}'::INTEGER[])) AND (eventextra = 'Q1''11'::text)) OR ((evcode = ANY ('{505,101,104,105,106,107,108,111,103,50
  44. 6,201,204,205,206,208,209,211,212,220,214,216,303,310}'::INTEGER[])) AND (eventdate > '2011-04-19 00:00:00'::TIMESTAMP WITHOUT TIME zone)))
  45.                                                    ->  BitmapOr  (cost=990.30..990.30 ROWS=23197 width=0) (actual TIME=157.690..157.690 ROWS=0 loops=1)
  46.                                                          ->  Bitmap INDEX Scan ON companyevents_evcode_idx  (cost=0.00..817.55 ROWS=23186 width=0) (actual TIME=7.004..7.004 ROWS=13988 loops=1)
  47.                                                                INDEX Cond: (evcode = ANY ('{305,307,306,308,309}'::INTEGER[]))
  48.                                                          ->  Bitmap INDEX Scan ON companyevents_eventdate_evcode_idx  (cost=0.00..172.75 ROWS=10 width=0) (actual TIME=150.677..150.677 ROWS=4828 loops=1)
  49.                                                                INDEX Cond: ((eventdate > '2011-04-19 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (evcode = ANY ('{505,101,104,105,106,107,108,111,103,506,201,2
  50. 04,205,206,208,209,211,212,220,214,216,303,310}'::INTEGER[])))
  51.                                              ->  Materialize  (cost=200.24..212.28 ROWS=1204 width=51) (actual TIME=0.000..0.012 ROWS=77 loops=2426)
  52.                                                    ->  Seq Scan ON evconfig cfg  (cost=0.00..199.04 ROWS=1204 width=51) (actual TIME=0.017..0.573 ROWS=77 loops=1)
  53.                                        ->  INDEX Scan USING companyclassinfo_exportcik_idx ON companyclassinfo ci  (cost=0.00..8.90 ROWS=2 width=75) (actual TIME=0.007..0.009 ROWS=1 loops=2426)
  54.                                              INDEX Cond: (ci.exportcik = ev.cik)
  55.                                  ->  INDEX Scan USING watchlist_daily_scores_new_pk ON watchlist_daily_scores wd  (cost=0.00..6.28 ROWS=1 width=44) (actual TIME=0.005..0.005 ROWS=1 loops=2917)
  56.                                        INDEX Cond: ((wd.cik = ci.exportcik) AND (wd.period = 30))
  57.                            ->  INDEX Scan USING rnsniplatest_pk ON rnsniplatest snip  (cost=0.00..6.28 ROWS=1 width=957) (actual TIME=0.004..0.004 ROWS=0 loops=2917)
  58.                                  INDEX Cond: (snip.cik = ci.exportcik)
  59.                                  FILTER: (snip.publishdate > '2011-04-19 00:00:00'::TIMESTAMP WITHOUT TIME zone)
  60.                      ->  Append  (cost=0.00..102.02 ROWS=237 width=30) (actual TIME=0.048..0.057 ROWS=1 loops=2917)
  61.                            ->  Seq Scan ON quotes qd  (cost=0.00..18.25 ROWS=220 width=30) (actual TIME=0.000..0.000 ROWS=0 loops=2917)
  62.                                  FILTER: (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone)
  63.                            ->  INDEX Scan USING quotes_2004_pk ON quotes_2004 qd  (cost=0.00..7.05 ROWS=1 width=20) (actual TIME=0.006..0.006 ROWS=0 loops=2917)
  64.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  65.                            ->  INDEX Scan USING quotes_2005_pk ON quotes_2005 qd  (cost=0.00..7.09 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  66.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  67.                            ->  INDEX Scan USING quotes_2006_pk ON quotes_2006 qd  (cost=0.00..7.17 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  68.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  69.                            ->  INDEX Scan USING quotes_2007_pk ON quotes_2007 qd  (cost=0.00..8.15 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  70.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  71.                            ->  INDEX Scan USING quotes_2008_pk ON quotes_2008 qd  (cost=0.00..8.33 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  72.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  73.                            ->  INDEX Scan USING quotes_2009_pk ON quotes_2009 qd  (cost=0.00..7.43 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  74.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  75.                            ->  INDEX Scan USING quotes_2010_pk ON quotes_2010 qd  (cost=0.00..7.37 ROWS=1 width=20) (actual TIME=0.005..0.005 ROWS=0 loops=2917)
  76.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  77.                            ->  INDEX Scan USING quotes_2011_pk ON quotes_2011 qd  (cost=0.00..6.63 ROWS=1 width=20) (actual TIME=0.006..0.007 ROWS=1 loops=2917)
  78.                                  INDEX Cond: ((qd.cid = ci.cid) AND (qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  79.                            ->  INDEX Scan USING quotes_2012_td_idx ON quotes_2012 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  80.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  81.                            ->  INDEX Scan USING quotes_2013_td_idx ON quotes_2013 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  82.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  83.                            ->  INDEX Scan USING quotes_2014_td_idx ON quotes_2014 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  84.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  85.                            ->  INDEX Scan USING quotes_2015_td_idx ON quotes_2015 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  86.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  87.                            ->  INDEX Scan USING quotes_2016_td_idx ON quotes_2016 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  88.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  89.                            ->  INDEX Scan USING quotes_2017_td_idx ON quotes_2017 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  90.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  91.                            ->  INDEX Scan USING quotes_2018_td_idx ON quotes_2018 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  92.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  93.                            ->  INDEX Scan USING quotes_2019_td_idx ON quotes_2019 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  94.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  95.                            ->  INDEX Scan USING quotes_2020_td_idx ON quotes_2020 qd  (cost=0.00..2.73 ROWS=1 width=30) (actual TIME=0.001..0.001 ROWS=0 loops=2917)
  96.                                  INDEX Cond: ((qd.trade_date >= '2004-01-01 00:00:00'::TIMESTAMP WITHOUT TIME zone) AND (qd.trade_date = ev.eventdate))
  97.  Total runtime: 723.502 ms
  98. (69 ROWS)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!