Content-Type: multipart/mixed; boundary="------------swM0TUTpjOHUAFQI22cq7r0X"
Message-ID: <cf154ef9-6bac-d268-b735-67a3443debba@enterprisedb.com>
Date: Sun, 11 Jun 2023 14:41:27 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Content-Language: en-US
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Subject: Should heapam_estimate_rel_size consider fillfactor?
Archived-At: <https://www.postgresql.org/message-id/cf154ef9-6bac-d268-b735-67a3443debba%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------swM0TUTpjOHUAFQI22cq7r0X
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi,

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.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------swM0TUTpjOHUAFQI22cq7r0X
Content-Type: text/x-patch; charset=UTF-8; name="relsize-fillfactor-fix.patch"
Content-Disposition: attachment; filename="relsize-fillfactor-fix.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90YWJsZS90YWJsZWFtLmMgYi9zcmMv
YmFja2VuZC9hY2Nlc3MvdGFibGUvdGFibGVhbS5jCmluZGV4IGE1ZTZjOTJmMzUuLjNhMjY4
NDZmMDEgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90YWJsZS90YWJsZWFtLmMK
KysrIGIvc3JjL2JhY2tlbmQvYWNjZXNzL3RhYmxlL3RhYmxlYW0uYwpAQCAtNzM3LDExICs3
MzcsMTkgQEAgdGFibGVfYmxvY2tfcmVsYXRpb25fZXN0aW1hdGVfc2l6ZShSZWxhdGlvbiBy
ZWwsIGludDMyICphdHRyX3dpZHRocywKIAkJICogYW5kIChjKSBkaWZmZXJlbnQgdGFibGUg
QU1zIG1pZ2h0IHVzZSBkaWZmZXJlbnQgcGFkZGluZyBzY2hlbWVzLgogCQkgKi8KIAkJaW50
MzIJCXR1cGxlX3dpZHRoOworCQlpbnQJCQlmaWxsZmFjdG9yOworCisJCS8qCisJCSAqIFdp
dGhvdXQgcmVsdHVwbGVzL3JlbHBhZ2VzLCB3ZSBhbHNvIG5lZWQgdG8gY29uc2lkZXIgZmls
bGZhY3Rvci4KKwkJICogVGhlIG90aGVyIGJyYW5jaCBjb25zaWRlcnMgaXQgaW1wbGljaXRs
eSBieSBjYWxjdWxhdGluZyBkZW5zaXR5CisJCSAqIGZyb20gYWN0dWFsIHJlbHBhZ2VzL3Jl
bHR1cGxlcyBzdGF0aXN0aWNzLgorCQkgKi8KKwkJZmlsbGZhY3RvciA9IFJlbGF0aW9uR2V0
RmlsbEZhY3RvcihyZWwsIEhFQVBfREVGQVVMVF9GSUxMRkFDVE9SKTsKIAogCQl0dXBsZV93
aWR0aCA9IGdldF9yZWxfZGF0YV93aWR0aChyZWwsIGF0dHJfd2lkdGhzKTsKIAkJdHVwbGVf
d2lkdGggKz0gb3ZlcmhlYWRfYnl0ZXNfcGVyX3R1cGxlOwogCQkvKiBub3RlOiBpbnRlZ2Vy
IGRpdmlzaW9uIGlzIGludGVudGlvbmFsIGhlcmUgKi8KLQkJZGVuc2l0eSA9IHVzYWJsZV9i
eXRlc19wZXJfcGFnZSAvIHR1cGxlX3dpZHRoOworCQlkZW5zaXR5ID0gKHVzYWJsZV9ieXRl
c19wZXJfcGFnZSAqIGZpbGxmYWN0b3IgLyAxMDApIC8gdHVwbGVfd2lkdGg7CiAJfQogCSp0
dXBsZXMgPSByaW50KGRlbnNpdHkgKiAoZG91YmxlKSBjdXJwYWdlcyk7CiAK

--------------swM0TUTpjOHUAFQI22cq7r0X--