Re: Need help with complicated SQL statement - Mailing list pgsql-general

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@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz