From: | Ken Geis <kgeis(at)speakeasy(dot)org> |
---|---|
To: | Bruno Wolff III <bruno(at)wolff(dot)to> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: bad estimates |
Date: | 2003-08-29 03:46:00 |
Message-ID: | 3F4ECC78.7090502@speakeasy.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:00:32 -0700,
> Ken Geis <kgeis(at)speakeasy(dot)org> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2003-08-29 04:05:19 | Re: bad estimates |
Previous Message | Eko Pranoto | 2003-08-29 03:44:40 | PostgreSQL HDD Grow capacity |