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
