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
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 |
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 |