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

On Tue, Nov 21, 2017 at 05:22:18PM +0530, Amit Kapila wrote:
> I think if we update the stats in copy_heap_data after copying the
> data, then we don't see such problem. Attached patch should fix the
> issue. You can try this patch to see if it fixes the issue for you.
> You might want to wait for a day or so to see if anyone else has any
> opinion on the patch or my analysis.

I'd love to but I wont be able to now for a week or two. The DB in question
is moving towards liveness but, once it's live I can work on a copy to see
if things become good. If I can get that happening sooner I'll grab that
chance.

> > 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;
>
> I think the reason for getting totally off stats during
> estimate_rel_size is that for the second column you have set
> statistics to 0. I think if you keep it to default or some reasonable
> number, then you won't get such a behavior. Anyhow, I think

Hmm. I wanted Postgres to ignore that column as it'll never be searched
on or sorted by or anything else. It's just there to provide a result.

Unless I missed the boat on this I'd like to keep that.

> irrespective of the value of stats, the relcache entry should also be
> updated as explained above.

Should the STATISTICS setting change index layout so drastically at
any rate?

AP