Re: [HACKERS] Index not used on simple select

Ole Gjerde <gjerde@icebox.org> writes:
> parts=> explain select * from av_parts where nsn = '123456';
> Seq Scan on av_parts  (cost=194841.86 rows=3206927 width=124)
> [ why isn't it using the index on nsn? ]

That is darn peculiar.  You're probably managing to trigger some nitty
little bug in the optimizer, but I haven't the foggiest what it might
be.

> Indices:  av_parts_itemid_key
>           av_parts_nsn_index
>           av_parts_partnumber_index

One bit of info you didn't provide is how that third index is defined.

Shipping your 4-million-row database around is obviously out of the
question, but I think a reproducible test case is needed; it's going to
take burrowing into the code with a debugger to find this one.  Can
you make a small test case that behaves the same way?  (One thing
to try right away is loading the same table and index definitions into
an empty database, but *not* loading any data and not doing vacuum.
If that setup doesn't show the bug, try adding a couple thousand
representative rows from your real data, vacuum analyzing, and then
seeing if it happens.)
        regards, tom lane