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

On Thu, Nov 23, 2017 at 11:01 AM, AP <pgsql(at)inml(dot)weebeastie(dot)net> wrote:
> 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.
>

Okay.

>> > 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?
>

Ideally not, that's why I proposed a patch to fix the actual cause of
the problem.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com