Row estimates for empty tables

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: postgre <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Row estimates for empty tables
Date: 2020-07-24 04:01:25
Message-ID: F02298E0-6EF4-49A1-BCB6-C484794D9ACC@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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(at)thebuild(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Rowley 2020-07-24 04:56:49 Re: Row estimates for empty tables
Previous Message Adrian Klaver 2020-07-23 23:16:07 Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2020-07-24 04:56:49 Re: Row estimates for empty tables
Previous Message Peter Geoghegan 2020-07-24 03:51:24 Re: heap_abort_speculative() sets xmin to Invalid* without HEAP_XMIN_INVALID