From: Christian Brink <cbrink@r-stream.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: peger@automotive.com
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Date: Wed, 24 Mar 2010 12:06:35 -0400
Message-ID: <4BAA388B.7090301@r-stream.com> (raw)
In-Reply-To: <12444.1269285695@sss.pgh.pa.us>
References: <4BA7C057.9090909@r-stream.com>
<12444.1269285695@sss.pgh.pa.us>
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
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: cbrink@r-stream.com, tgl@sss.pgh.pa.us, peger@automotive.com
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
In-Reply-To: <4BAA388B.7090301@r-stream.com>
* 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