Jack Orenstein <jack.orenstein@hds.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
