Re: How does the planner determine plan_rows ?

>>>>> "Donald" == Donald Dong <xdong(at)csumb(dot)edu> writes:

Donald> Hi,
Donald> I created some empty tables and run ` EXPLAIN ANALYZE` on
Donald> `SELECT * `. I found the results have different row numbers,
Donald> but the tables are all empty.

Empty tables are something of a special case, because the planner
doesn't assume that they will _stay_ empty, and using an estimate of 0
or 1 rows would tend to create a distorted plan that would likely blow
up in runtime as soon as you insert a second row.

The place to look for info would be estimate_rel_size in
optimizer/util/plancat.c, from which you can see that empty tables get
a default size estimate of 10 pages. Thus:

Donald> =# CREATE TABLE t1(id INT, data INT);
Donald> =# EXPLAIN ANALYZE SELECT * FROM t1;
Donald> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8) (actual
Donald> time=0.003..0.003 rows=0 loops=1)

An (int,int) tuple takes about 36 bytes, so you can get about 226 of
them on a page, so 10 pages is 2260 rows.

Donald> =# CREATE TABLE t2(data VARCHAR);
Donald> =# EXPLAIN ANALYZE SELECT * FROM t2;
Donald> Seq Scan on t2 (cost=0.00..23.60 rows=1360 width=32) (actual
Donald> time=0.002..0.002 rows=0 loops=1)

Size of a varchar with no specified length isn't known, so the planner
determines an average length of 32 by the time-honoured method of rectal
extraction (see get_typavgwidth in lsyscache.c), making 136 rows per
page.

--
Andrew (irc:RhodiumToad)