query to get latest from many to many join table postgres

Question

I have 3 tables. Stocks have many News articles and News articles can refer to 1 or more Stocks. This is modelled with a Stock table, a News table and a Stock_News table.

How would I get the latest News article for say 30 stock symbols I provide? What indices would make this most efficient?

My News table has id, link, published_at. (index on published_at, id primary key)

My Stocks table has id, symbol. (index on symbol, id primary key)

My stock_news table has stock_id news_id. (index on each individually and combined)

Currently I am using but i as wondering if this is the best way

SELECT n.link, s.symbol, n.published_at FROM news n 
JOIN stock_news sn on n.id = sn.news_id 
JOIN stocks s on s.id = sn.stock_id where s.symbol in ('AAPL', 'GOOG' ... etc) 
ORDER BY n.published_at DESC;

The EXPLAIN query on some demo data shows:

 Sort  (cost=8.92..8.92 rows=1 width=115)
   Sort Key: n.published_at DESC
   ->  Nested Loop  (cost=3.50..8.92 rows=1 width=115)
         ->  Hash Join  (cost=3.45..7.51 rows=1 width=12)
               Hash Cond: (s.id = sn.stock_id)
               ->  Seq Scan on stocks s  (cost=0.00..4.05 rows=2 width=12)
                     Filter: ((symbol)::text = ANY ('{AAPL,GOOG}'::text[]))
               ->  Hash  (cost=2.67..2.67 rows=223 width=16)
                     ->  Seq Scan on stock_news sn  (cost=0.00..2.67 rows=223 width=16)
         ->  Index Scan using news_pkey on news n  (cost=0.05..1.40 rows=1 width=119)
               Index Cond: (id = sn.news_id)

looks good, but you might want to check out the code review forum — vol7ron
– vol7ron, Commented Mar 15, 2018 at 4:01

Gordon Linoff · Accepted Answer · 2018-03-15 04:01:40Z

1

If you want the latest, I would recommend distinct on:

SELECT DISTINCT ON (s.symbol) n.link, s.symbol, n.published_at
FROM news n JOIN
     stock_news sn
     ON n.id = sn.news_id JOIN
     stocks s
     ON s.id = sn.stock_id 
WHERE s.symbol IN ('AAPL', 'GOOG' ... etc) 
ORDER BY s.symbol, n.published_at DESC;

For performance you want indexes on: stocks(symbol, id), stock_news(stock_id, new_id), and news(id).

answered Mar 15, 2018 at 4:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vol7ron Over a year ago

Yep, +1, except that SQL looks funny to me :)

Terence Chow Over a year ago

why not an index on news(published_at)? Also, when you say stocks(symbol,id) is that a multicolumn index or 2 separate indices?

Gordon Linoff Over a year ago

@TerenceChow . . . published_at is the second key in the order by. An index is useful for that.

Collectives™ on Stack Overflow

query to get latest from many to many join table postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related