Re: Postgres optimizer choosing wrong index

Tom Lane wrote:
> Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> writes:
>> Tom Lane wrote:
>>> If you plug in a value that *does* occur in the table it should probably
>>> choose the more-relevant index consistently.
>
>> Unfortunately, it matters a lot at runtime. The dh value is not very selective,
>> as shown by the statistics above.
>
> A dh value that does not occur in the index is *perfectly* selective.
> I'm not sure what your problem is but this example isn't illustrating
> anything wrong that I can see.

I see your point.

I may have simplified too far. Our application runs a number of different
queries. All our WHERE clauses restrict dh and fh. For a given pair of (dh, fh)
values, the initial query should come up empty and then insert this pair, and
then there is further processing (SELECT, UPDATE). Something is causing a huge
number of index row reads (according to pg_stat_user_indexes) but only in tables
that have been vacuumed.

Jack