Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster

From: Christian Brink <cbrink(at)r-stream(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org, peger(at)automotive(dot)com
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Date: 2010-03-24 16:06:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2010-03-25 00:46:49
Subject: Re: Forcing index scan on query produces 16x faster
Previous:From: Chris BrowneDate: 2010-03-24 15:41:05
Subject: Re: mysql to postgresql, performance questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group