Re: Need help with complicated SQL statement

Shane Ambler wrote:
  > I INSERTed 500 stocks entries and 10,000 stockprices entries for each
> stock (that's 5,000,000 price rows), then from
>
> EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id = 20
> I got - Total runtime: 981.618 ms
>
> EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id IN
> (10,25,36,45,86,154,368,481)
> I got - Total runtime: 8084.217 ms
>

Actually I found a better way - after you run the example I gave you
before -

DROP INDEX idx_stockprices_date
DROP INDEX idx_stockprices_stock_id

CREATE INDEX idx_stockprices_id_date ON stockprices (stock_id,price_date);


with the same data (5,000,000 price rows) I then get -

EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id = 20
I got - Total runtime: 6.397 ms

EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id IN
(10,25,36,45,86,154,368,481)
I got - Total runtime: 36.265 ms


Which is probably the speed you want ;-)



--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz