Hi,
On 2023-06-11 14:41:27 +0200, Tomas Vondra wrote:
> While testing some stuff, I noticed heapam_estimate_rel_size (or rather
> table_block_relation_estimate_size it calls) ignores fillfactor, so that
> for a table without statistics it ends up with reltuple estimate much
> higher than reality. For example, with fillfactor=10 the estimate is
> about 10x higher.
>
> I ran into this while doing some tests with hash indexes, where I use
> fillfactor to make the table look bigger (as if the tuples were wider),
> and I ran into this:
>
> drop table hash_test;
> create table hash_test (a int, b text) with (fillfactor=10);
> insert into hash_test select 1 + ((i - 1) / 10000), md5(i::text)
> from generate_series(1, 1000000) s(i);
> -- analyze hash_test;
> create index hash_test_idx on hash_test using hash (a);
>
> select pg_size_pretty(pg_relation_size('hash_test_idx'));
>
> If you run it like this (without the analyze), the index will be 339MB.
> With the analyze, it's 47MB.
>
> This only happens if there are no relpages/reltuples statistics yet, in
> which case table_block_relation_estimate_size estimates density from
> tuple width etc.
>
> So it seems the easiest "fix" is to do ANALYZE before creating the index
> (and not after it, as I had in my scripts). But I wonder how many people
> fail to realize this - it sure took me a while to realize the indexes
> are too large and even longer what is causing it. I wouldn't be very
> surprised if many people had bloated hash indexes after bulk loads.
>
> So maybe we should make table_block_relation_estimate_size smarter to
> also consider the fillfactor in the "no statistics" branch, per the
> attached patch.
Seems like a good idea - I can't think of a reason why we shouldn't do so.
Greetings,
Andres Freund