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