Re: Should heapam_estimate_rel_size consider fillfactor?

On 1/29/25 19:56, Heikki Linnakangas wrote:
> 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.
> 

Thanks for the report. And yeah, clamping it to 1 seems like the right
fix for this. I wonder if it's worth inventing some sort of test for
this, shouldn't be too hard I guess.

In any case, I'll take care of the fix/backpatch soon.


regards

-- 
Tomas Vondra