Is driving my high costs on the inner loop. The index has 2-5 million pages depending on the partition . If I run this against 9.2.2 with / 10000.0 the estimate is even higher.
from 9.3 and I play I can make this work I can it pick the plan on some partitions with product_id=2 but not product_id=1. If I remove the fudge-factor cost adjustment line I get the nested-loop plan always.
That was only temporarily the formula during 9.3dev. Tom re-did that entire part of the code rather substantially in the current tip of 9.3 (commit 31f38f28b00cbe2b). Now it is based on the number of tuples, and the height, rather than pages, and is multiplied by the cpu_operator_cost not the random_page_cost.
Breaking the index into smaller partial indexes for each year seems to be giving me the plans I want with random_page_cost=2 (I might also try partial indexes on the month).
Even with the 9.3 log based fudge-factor we are seeing the fudge-factor being big enough so that the planner is picking a table scan over the index.
Have you tried it under 9.3 HEAD, rather than just back-porting the temporary
If you are trying to make your own private copy of 9.2, then removing the fudge factor altogether is probably the way to go. But if you want to help improve future versions, you probably need to test with the most up-to-date dev version.
A lot of loop iterations can be satisfied by cached pages of the index the fudge-factor doesn't really account for this.
Setting random_page_cost to 2 is already telling it that most of fetches are coming from the cache. Of course for the upper blocks of an index even more than "most" are likely to be, but the latest dev code takes care of that.