On Wed, Sep 28, 2016 at 6:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
[ Please don't re-quote the entire damn thread in each followup. Have some respect for your readers' time, and assume that they have already seen the previous traffic, or could go look it up if they haven't. The point of quoting at all is just to quickly remind people where we are in the discussion. ]
Sorry, understood.
If you say "well yeah, but it seems to perform fine when I force it to use that index anyway", the answer may be that you need to adjust random_page_cost. The default value is OK for tables that are mostly sitting on spinning rust, but if your database is RAM-resident or SSD-resident you probably want a value closer to 1.
Ahhh, this could absolutely be the key right here. I could totally see why it would make sense for the planner to do what it's doing given that it's weighting sequential access more favorably than random access.
Beautiful! After changing the random_page_cost to 1.0 the original query went from ~3.5s to ~35ms. This is exactly the kind of insight I was fishing for in the original post. I'll keep in mind that the query planner is very tunable and has these sorts of hardware-related trade-offs in the future. I can't thank you enough!