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@xxxxxxxxxx
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
