Thread: reindex

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

use truncate instead of delete ?

Sim Zacks <sim@compulab.co.il> writes:
> 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.

Are you vacuuming each time you erase-and-repopulate?

The truncate approach might work too, but it would depend on whether you
can lock out all access to the table while you refill it.

            regards, tom lane