From: Ken Geis <kgeis@speakeasy.org>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
Date: Thu, 28 Aug 2003 20:46:00 -0700
Message-ID: <3F4ECC78.7090502@speakeasy.org> (raw)
In-Reply-To: <20030829033818.GA5577@wolff.to>
References: <3F4E99F7.7080604@speakeasy.org>
<20030829025109.GD4373@wolff.to>
<3F4EBF7E.8020602@speakeasy.org>
<20030829030156.GF4373@wolff.to>
<3F4EC1D0.5010503@speakeasy.org>
<20030829033818.GA5577@wolff.to>
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 20:00:32 -0700,
> Ken Geis <kgeis@speakeasy.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgeis@speakeasy.org, bruno@wolff.to
Subject: Re: bad estimates
In-Reply-To: <3F4ECC78.7090502@speakeasy.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox