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

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