Researching this some more, it appears to be the case that VACUUM (by itself, no
ANALYZE) is changing the optimizer's behavior. Here is a self-contained test:
select '*** drop t';
drop table t cascade;
select '*** create t(dh, fh, nm, filler)';
create table t (dh int, fh int, nm int, filler char(500));
select '*** create index on (dh, fh)';
create index idx_df on t(dh, fh);
select '*** create index on (dh, nm)';
create index idx_dn on t(dh, nm);
select '*** explain select * from t where dh = 1 and fh = 2';
explain select * from t where dh = 1 and fh = 2;
select '*** vacuum t (no analyze)';
vacuum t;
select '*** explain select * from t where dh = 1 and fh = 2';
explain select * from t where dh = 1 and fh = 2;
This output was produced by 7.4.8. Version 8.3.4 uses the "wrong" execution plan
both before and after the vacuum.
Jack
