I have an aggregate table which is constantly being overwritten. Every
10 minutes or so, the table is erased and populated with new data, most
of which is the same.
Basically a materialized view.
I have been going through some queries that use this table and noticed
that the explain looked different from our test database (a backup of
the prod db) which has the same number of rows and the same type of
data. vacuum analyze did not change anything.
One specific difference I noticed was that it was doing a sequential
scan instead of an index scan.
reindex table changed the prod one to look like the test one.
The indexes are all btree.
After an hour or so the old explain had returned.
Does this make any sense? Should I be doing something different?
Thanks
Sim