Re: Postgres optimizer choosing wrong index

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