Re: Using the wrong index (very suboptimal), why?

"Shane Wright" <shane(dot)wright(at)edigitalresearch(dot)com> writes:
> It's not that it isn't using any index (so enable_seqscan=off doesn't
> help), it's that the index it picks is suboptimal.

> The query is based on 3 of the table columns - there is an index on all
> three, but it prefers to use an index on just two of them, then
> filtering by the 3rd (EXPLAIN output is below - showing the query as
> well).

Well, that isn't ipso facto a stupid choice to make. The 3-column
index is larger and slower to search, so it should only use it if
there's a substantial reduction in the number of heap rows to be
visited. You say that there is, but the estimated costs in the EXPLAINs
you provide sure don't indicate that the planner thinks so. You need to
look into what the estimated vs actual rowcounts are for just the
two-column condition (ie, where nid = something AND iid1 = something).
Getting those estimates to match reality is the key to getting a sane
plan choice here.

regards, tom lane