Message-ID: <3F4ECC78.7090502@speakeasy.org>
Date: Thu, 28 Aug 2003 20:46:00 -0700
From: Ken Geis <kgeis@speakeasy.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.4) Gecko/20030612
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
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>
In-Reply-To: <20030829033818.GA5577@wolff.to>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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