Message-ID: <3F4ED1DC.50808@speakeasy.org>
Date: Thu, 28 Aug 2003 21:09:00 -0700
From: Ken Geis <kgeis@speakeasy.org>
Reply-To: kgeis@alum.calberkeley.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>
	<3F4ECC78.7090502@speakeasy.org> <20030829040519.GA5746@wolff.to>
In-Reply-To: <20030829040519.GA5746@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:46:00 -0700,
>   Ken Geis <kgeis@speakeasy.org> 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