Re: Postgres optimizer choosing wrong index

Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> writes:
> 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.

Well, that's a bit more concrete but it's still difficult to tell where
the problem is. Are you by any chance creating new tables and then
vacuuming them while they're still empty? That would cause
pg_class.relpages to get set to zero, and 7.4.x is not bright enough to
change plans when you later fill the table (until you do another vacuum
or analyze). However, I think 7.4 would always choose a seqscan on a
table it thinks is zero-size, so I'm not sure that that's what's going
on here.

regards, tom lane