Re: Should heapam_estimate_rel_size consider fillfactor?

On 03/07/2023 20:54, Tomas Vondra wrote:
> Pushed, using the formula with two divisions (as in the original patch).

I ran into an issue with this, in the case of a small fillfactor and 
wide tuple width:

On v16:

postgres=# create table t (data char(900)) with (fillfactor = 10, 
autovacuum_enabled=off);
CREATE TABLE
postgres=# insert into t select g from generate_series(1, 1000) g;
INSERT 0 1000
postgres=# explain select count(*) from t;
                          QUERY PLAN
-------------------------------------------------------------
  Aggregate  (cost=1025.00..1025.01 rows=1 width=8)
    ->  Seq Scan on t  (cost=0.00..1020.00 rows=2000 width=0)
(2 rows)

On v17:
                         QUERY PLAN
----------------------------------------------------------
  Aggregate  (cost=1000.00..1000.01 rows=1 width=8)
    ->  Seq Scan on t  (cost=0.00..1000.00 rows=1 width=0)
(2 rows)

The new estimeate is 1 row, which is bad. Didn't change the plan in this 
case, but I originally saw this in a test with more rows, and the 
planner would not choose a parallel scan for the query because of that.

The calculation table_block_relation_estimate_size() in this case is:

tuple_width=3604
overhead_bytes_per_tuple=28
fillfactor=10
usable_bytes_per_page=8168
density = (usable_bytes_per_page * fillfactor / 100) / tuple_width

which gets rounded down to 0.

The straightforward fix is to clamp it to 1. The executor will always 
place at least one tuple on a page, regardless of fillfactor.

-- 
Heikki Linnakangas
Neon (https://neon.tech)