Ted Byers wrote:
It gave apparently correct values, but for some
reason, it insisted on returning thousands upon
thousands of identical record. There is something
awry there, but I can't place what. Yes, I know I
could use SELECT DISTINCT, but I worry that it may be
doing a full table scan, as opposed to the relatively
direct lookup I came up with after looking at your
statement. I don't yet know how long it would take
because it is the slowest option I tied, and I gave up
after it had returned over 10,000 rows and still
showed no signs of finishing. I don't understand this
as explain returned apparently much better results for
yours than it did for mine.
Now that I look at it again today I see that - you would either need to
use SELECT DISTINCT(stock_id) in the VIEW definition or select the
stock_id from the stock table instead of the stockprices table.
I set up a little test this time - this is the example I came up with -
CREATE DATABASE stocktest;
\c stocktest
CREATE TABLE stocks
(
id serial PRIMARY KEY,
description text
);
CREATE TABLE stockprices
(
id serial PRIMARY KEY,
stock_id integer REFERENCES stocks (id),
stock_price numeric,
price_date date
);
CREATE INDEX idx_stockprices_date ON stockprices (price_date);
CREATE INDEX idx_stockprices_stock_id ON stockprices (stock_id);
CREATE VIEW stock_prices_combined AS
SELECT
id AS stock_id
, (SELECT stock_price FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1) as one_adjusted
, (SELECT price_date FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1) as one_date
, (SELECT stock_price FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 22) as two_adjusted
, (SELECT price_date FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 22) as two_date
, (SELECT stock_price FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 66) as three_adjusted
, (SELECT price_date FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 66) as three_date
, (SELECT stock_price FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 132) as four_adjusted
, (SELECT price_date FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 132) as four_date
, (SELECT stock_price FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 264) as five_adjusted
, (SELECT price_date FROM stockprices WHERE stock_id = ST.id ORDER BY
price_date DESC LIMIT 1 OFFSET 264) as five_date
FROM stocks ST;
CREATE VIEW stock_price_history AS
SELECT
stock_id,
one_date AS pd1,
one_adjusted AS current_price,
two_date AS pd22,
100.0 * (one_adjusted - two_adjusted)/two_adjusted AS gl22pc,
three_date AS pd66,
100.0 * (one_adjusted - three_adjusted)/three_adjusted AS gl66pc,
four_date AS pd132,
100.0 * (one_adjusted - four_adjusted)/four_adjusted AS gl132pc,
five_date AS pd264,
100.0 * (one_adjusted - five_adjusted)/five_adjusted AS gl264pc
FROM stock_prices_combined;
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
So that's about 1 second per stock_id returned (on my old machine).
You can change that last query to be -
SELECT * FROM stock_price_history WHERE stock_id IN (SELECT stock_id
FROM sometable WHERE ....)
Which gives you the range of stock_id's from a table that you asked about.
--
Shane Ambler
pgSQL@xxxxxxxxxx
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
