Re: 10.1: hash index size exploding on vacuum full analyze

On Mon, Nov 20, 2017 at 01:26:50PM +0530, Amit Kapila wrote:
> Another angle to look at it is that even if the values of relpages and
> reltuples is not updated why we get such a wrong estimation by
> estimate_rel_size. I think to some extent it depends on the schema of
> the table, so is it possible for you to share schema of the table.

Hi,

Schema's simple:

CREATE TABLE link (
datum_id BYTEA NOT NULL,
ids BYTEA NOT NULL
);
ALTER TABLE link ALTER COLUMN datum_id SET STATISTICS 10000;
ALTER TABLE link ALTER COLUMN ids SET STATISTICS 0;
ALTER TABLE link SET ( AUTOVACUUM_ANALYZE_SCALE_FACTOR = 0.001, AUTOVACUUM_VACUUM_SCALE_FACTOR = 0.001 );
CREATE INDEX ON link USING hash (datum_id) WITH ( FILLFACTOR = 90 );

That's for the live table. Then I move it aside and recreate the index
with FILLFACTOR = 100.

> > Tell me if you need me to keep the index around.
>
> I don't think so, but till we solve the problem there is no harm in
> keeping it if possible because one might want some information at a
> later stage to debug this problem. OTOH, if you have space crunch
> then feel free to delete it.

No worries. I'll keep it around for as long as I can.

AP