Martijn van Oosterhout <kleptog@svana.org> writes:
> What I'm more curious
> about is why the REINDEX caused it to forget the statistics, thus making it
> use the brain-dead index scan again.
Both CREATE INDEX and REINDEX are coded to put the initial default
relpages/reltuples values (10/1000) into the table's pg_class entry,
rather than the true counts that they computed as a byproduct of
building the index, if the true tuple count is zero. The motivation for
this is that if you do CREATE TABLE and then immediately CREATE INDEX
before loading up any data, you don't want the default values to be
replaced by zeroes --- that would make performance go to heck as soon as
any reasonable amount of data gets loaded into the table. (The defaults
are chosen with malice aforethought to be large enough to prompt
indexscans.)
VACUUM, on the other hand, figures it's okay to mark an empty table
as empty.
REINDEX behaves the way it does because it's built on top of CREATE
INDEX. I'm not sure that the it-might-be-a-brand-new-table argument
should be applied to REINDEX though. Maybe it'd be better to go ahead
and store the zeroes in that case.
regards, tom lane