On Thu, Jun 30, 2011 at 1:53 AM, Samuel Gendler
<sgendler@ideasculptor.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.
