Re: Wrong rows count in EXPLAIN

On Wed, Apr 27, 2022 at 09:44:21AM -0400, Tom Lane wrote:
> =?koi8-r?B?8MHO1MDbyc4g4czFy9PBzsTSIOnXwc7P18ne?= <AI(dot)Pantyushin(at)gaz-is(dot)ru> writes:
> > When I create a new table, and then I evaluate the execution of the SELECT query, I see a strange rows count in EXPLAIN
> > CREATE TABLE test1(f INTEGER PRIMARY KEY NOT NULL);
> > ANALYZE test1;
> > EXPLAIN SELECT * FROM test1;
> > QUERY PLAN
> > ---------------------------------------------------------
> > Seq Scan on test1 (cost=0.00..35.50 rows=2550 width=4)
> > (1 row)
>
> > Table is empty but rows=2550.
>
> This is intentional, arising from the planner's unwillingness to
> assume that a table is empty. It assumes that such a table actually
> contains (from memory) 10 pages, and then backs into a rowcount
> estimate from that depending on the data-type-dependent width of
> the table rows.
>
> Without this provision, we'd produce very bad plans for cases
> where a newly-populated table hasn't been analyzed yet.

We could have a noice mode that warns when a table without statistics is
used.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson