Re: Postgres optimizer choosing wrong index

Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> writes:
> - I created two schemas, NOVAC and VAC, each with a table T as described above.

> - Before loading data, I ran VACUUM ANALYZE on VAC.T.

> - I then started loading data. The workload is a mixture of INSERT, SELECT and
> UPDATE. For SELECT and UPDATE the WHERE clause always includes "dh = ? and fh = ?".

Basically your problem here is that vacuum records the size of the table
as zero (in pg_class.relpages/reltuples) and that causes the computed
costs of the two indexscans to be exactly the same, so it's a tossup
which one gets used. (In recent versions I think the index with higher
OID would typically get chosen in a tie, but I forget if 7.4 worked that
way.)

8.0 and up are smart enough not to believe pg_class.relpages anymore
after you've loaded a lot of data, but 7.4 isn't. In testing similar
cases here, I get reasonable cost estimates and a sane plan choice
from 7.4 so long as the stats are up to date.

Bottom line: you need to vacuum (or preferably analyze) *after*
initially populating a table, not before.

regards, tom lane