Row estimates for empty tables

Christophe Pettus <xof@thebuild.com>

From: Christophe Pettus <xof@thebuild.com>
To: postgre <pgsql-general@lists.postgresql.org>
Date: 2020-07-24T04:01:25Z
Lists: pgsql-hackers, pgsql-general
I realize I've never quite known this; where does the planner get the row estimates for an empty table?  Example:

psql (11.8)
Type "help" for help.

xof=# CREATE TABLE t (i integer, t text, j integer);
CREATE TABLE
xof=# VACUUM ANALYZE t;
VACUUM
xof=# EXPLAIN ANALYZE SELECT * FROM t;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.015..0.015 rows=0 loops=1)
 Planning Time: 5.014 ms
 Execution Time: 0.094 ms
(3 rows)

xof=# INSERT INTO t values(1, 'this', 2);
INSERT 0 1
xof=# EXPLAIN ANALYZE SELECT * FROM t;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.010..0.011 rows=1 loops=1)
 Planning Time: 0.039 ms
 Execution Time: 0.021 ms
(3 rows)

xof=# VACUUM ANALYZE t;
VACUUM
xof=# EXPLAIN ANALYZE SELECT * FROM t;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1.01 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=1)
 Planning Time: 0.069 ms
 Execution Time: 0.019 ms
(3 rows)

xof=# DELETE FROM t;
DELETE 0
xof=# VACUUM ANALYZE t;
VACUUM
xof=# EXPLAIN ANALYZE SELECT * FROM t;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..29.90 rows=1990 width=13) (actual time=0.004..0.004 rows=0 loops=1)
 Planning Time: 0.034 ms
 Execution Time: 0.015 ms
(3 rows)


--
-- Christophe Pettus
   xof@thebuild.com




Commits

  1. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.