Re: optimizer choosing the wrong index

Martin Below <machtin(dot)below(at)googlemail(dot)com> writes:
> Why is "idx_wrong" used (which only includes one of the fields
> queried) instead of idx_correct (which contains both fields)?

It's not immediately clear that that choice is "wrong". A two-column
index is bigger and hence more expensive to search than a one-column
index --- perhaps quite substantially so, given this particular
combination of datatypes. The planner is probably estimating that
the expires_on condition is selective enough that nothing much will be
gained by using the two-column index. If that's wrong (which is not
proven by your example) you might be able to fix it by increasing
the stats target for the table. Also, if you haven't customized
effective_cache_size, increasing that makes large indexes look
cheaper to use.

regards, tom lane