Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:46:00 -0700,
> Ken Geis <kgeis@speakeasy.org> wrote:
>>It is not the table or the query that is wrong. It is either the db
>>parameters or the optimizer itself.
...
>
> It is still odd that you didn't get a big speed up for just the min though.
> You example did have the stock id and the date as the primary key which
> would make sense since the stock id and stock price on a day wouldn't
> be guarenteed to be unique. Are you absolutely sure you have a combined
> key on the stock id and the stock price?
I am positive! I can send a log if you want, but I won't post it to the
list.
The arity on the data is roughly 1500 price_dates per stock_id.
I was able to get the query to return in a reasonable amount of time
(still ~3 minutes) by forcing a nested loop path using SQL functions
instead of min and max.
I'm going to run comparisons on 7.3.3 and 7.4-beta2. I'll also look
into the optimizer source to try to figure out why it thinks scanning
this index is so expensive.
Ken