Message-ID: <4BAA388B.7090301@r-stream.com>
Date: Wed, 24 Mar 2010 12:06:35 -0400
From: Christian Brink <cbrink@r-stream.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100217 Lightning/1.0b1 Shredder/3.0.3pre
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org, peger@automotive.com
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on
	query produces faster
References: <4BA7C057.9090909@r-stream.com> <12444.1269285695@sss.pgh.pa.us>
In-Reply-To: <12444.1269285695@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 03/22/2010 03:21 PM, Tom Lane wrote:
> The fundamental reason why you're getting a bad plan choice is the
> factor-of-100 estimation error here.  I'm not sure you can do a whole
> lot about that without rethinking the query --- in particular I would
> suggest trying to get rid of the non-constant range bounds.  You're
> apparently already plugging in an external variable for the date,
> so maybe you could handle the time of day similarly instead of joining
> to sysstrings for it.
>
>    

Tom & Peter,

I thought you might like to know the outcome of this. I was able to get 
the 8.0 and the 8.2 planner to correctly run the query. There were 2 
issues. As Tom pointed out the the 'systrings' lookup seems to be the 
main culprit. Which makes sense. How can the planner know how to run the 
query when it doesn't know approximately what it will bracket the until 
the query has started?

The other part of the solution is bit concerning. I had to do a 'dump 
and load' (and vacuum analyze)  to get the planner to work correctly 
even after I rewrote the query. FYI I had run 'VACUUM ANALYZE' (and 
sometimes 'REINDEX TABLE x') between each test.


-- 
Christian Brink