On Thu, Aug 28, 2003 at 20:00:32 -0700, Ken Geis <[EMAIL PROTECTED]> wrote:
Bruno Wolff III wrote:
Not according to the optimizer! Plus, this is not guaranteed to return the correct results.
For it to be fast you need an index on (stock_id, price_date) so that you can use an index scan.
I already said that such an index existed. In fact, it is the primary key of the table. And yes, I *am* analyzed!
Your original example didn't actually match that of the table you are showing examples from. In that example the second half of the primary key was the date not the end of the day price. If this is the case for the real table, then that is the reason the distinct on doesn't help.
I had obfuscated the table in the example and forgot to do the same with the query. Serves me right for thinking I care about that.
A big problem is that the values I am working with are *only* the primary key and the optimizer is choosing a table scan over an index scan. That is why I titled the email "bad estimates." The table has (stock_id, price_date) as the primary key, and a bunch of other columns. What I *really* want to do efficiently is
select stock_id, min(price_date), max(price_date) from day_ends group by stock_id;
It is not the table or the query that is wrong. It is either the db parameters or the optimizer itself.
Ken
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
