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