# How does the planner determine plan_rows ?

5 messages
Open this post in threaded view
|

## How does the planner determine plan_rows ?

 Hi, I created some empty tables and run ` EXPLAIN ANALYZE` on `SELECT * `. I found the results have different row numbers, but the tables are all empty. =# CREATE TABLE t1(id INT, data INT); =# EXPLAIN ANALYZE SELECT * FROM t1;   Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=8) (actual   time=0.003..0.003 rows=0 loops=1) =# CREATE TABLE t2(data VARCHAR); =# EXPLAIN ANALYZE SELECT * FROM t2;   Seq Scan on t2  (cost=0.00..23.60 rows=1360 width=32) (actual   time=0.002..0.002 rows=0 loops=1) =# CREATE TABLE t3(id INT, data VARCHAR); =# EXPLAIN ANALYZE SELECT * FROM t3;   Seq Scan on t3  (cost=0.00..22.70 rows=1270 width=36) (actual   time=0.001..0.001 rows=0 loops=1) I found this behavior unexpected. I'm still trying to find out how/where the planner determines the plan_rows. Any help will be appreciated! Thank you, Donald Dong
Open this post in threaded view
|

## Re: How does the planner determine plan_rows ?

 >>>>> "Donald" == Donald Dong <[hidden email]> 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)
Open this post in threaded view
|

## Re: How does the planner determine plan_rows ?

 Thank you for the great explanation! > On Jan 10, 2019, at 7:48 PM, Andrew Gierth <[hidden email]> wrote: > >>>>>> "Donald" == Donald Dong <[hidden email]> 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)