Re: near identical queries have vastly different plans

On Thu, Jun 30, 2011 at 1:53 AM, Samuel Gendler
<sgendler(at)ideasculptor(dot)com>wrote:

> If I could figure out either a query structure or an index structure which
> will force the fast query plan, I'd be much happier. So that is what I am
> looking for - an explanation of how I might convince the planner to always
> use the fast plan.
>
>
I eventually noticed that constraint_exclusion didn't seem to be working and
remembered that it only works when the filter is on the partitioned table
itself, not when the table is being filtered via a join. Adding a where
clause which limits f.time_fk to the appropriate range not only fixed
constraint_exclusion behaviour, but also caused the query planner to produce
the same plan for both versions of the query - a plan that was an order of
magnitude faster than the previous fastest plan. It went from 20 seconds
to just less than 2 seconds.