Re: Unexpected expensive index scan

On Wed, Sep 28, 2016 at 6:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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!

Cheers!