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

From Shane Ambler
Subject Re: Need help with complicated SQL statement
Date
Msg-id 47428E04.6020008@Sheeky.Biz
Whole thread Raw
In response to Re: Need help with complicated SQL statement  (Ted Byers <r.ted.byers@rogers.com>)
List pgsql-general
Ted Byers wrote:
> Thanks Shane,
>
> It works reasonably well.  It gets the right answer,
> but I guess my data set is much larger than your test.

What indexes have you got?

Using this index on the sample I sent gets the response time to about
5ms (per stock_id) (as opposed to 900ms with these columns indexed
separately)

CREATE INDEX idx_stockprices_id_date ON stockprices (stock_id,price_date);


> Please consider the appended data.
>
> The first two SQL statements are directly comparable.
> My Left join is marginally simpler, as shown by
> EXPLAIN, and runs to completion in about 3 seconds
> (elapsed real time), as opposed to about 30 seconds
> for the two views.  It makes a little sense, though,
> in that according to EXPLAIN, the LEFT JOIN needs to
> consider a dramatically smaller number of rows.  What
> I find puzzling, though, is that it takes less time to
> get the report for 28 stocks at a given time than it
> does to get the report for 1. (Both take about 30
> seconds, but for 28 stocks, it takes about 0.005
> seconds less time ;-)
>
> This is a case where LEFT JOINS appear to be much
> faster than subqueries.
>
> I appreciate all your help, but I am struggling to
> figure out how best to adapt my LEFT JOINs in your
> VIEWs, so that the latter benefit from the speed of
> the JOINs.  The heart of my problem is to figure out
> how to  use a stock_id in the WHERE clause.

That is where I have moved away from your select - the way you are
joining makes it hard to adapt to where you want it to end up (more than
one stock_id per query)

By using the view that generates the rows you want you make the query
sent from the client so much simpler and make it easy to get any single
or list of stock_id you want.

The second view will add little overhead and can be part of the select
sent from the client if you wish. I separated them in to two views to
prevent duplicating the same selects for the calculations. You can merge
them into one view if you wish - the first view would become a subselect
for the second view.


> One thing I am not certain of is, "Is there a way to
> preserve the logic of the WHERE clauses by replacing
> the WHERE clause, which I use to sample the time
> series at 22 days ago, 66 days ago, 132 days ago &c.,
> by a "GROUP BY" clause, grouping by stock_id?  If so,
> might that, along with an additional LEFT JOIN, get me
> the result I am after?
>
> I created a stored procedure that takes an id argument
> (and can usefully invoke it on any stock_id in the
> database), but the problem remains as to how to
> construct a record set by applying the procedure to
> each id in a set of ids returned, e.g., by SELECT
> stock_id FROM stocks;
>
> Ted
>

--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

pgsql-general by date:

Previous
From: Philip Semanchuk
Date:
Subject: plpythonu under OS X 10.4 -- a brief HOWTO
Next
From: Oleg Bartunov
Date:
Subject: Re: [pgsql-advocacy] PostgreSQL Conference 08 East!