...On Thu, Aug 28, 2003 at 20:46:00 -0700, Ken Geis <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
