From: | Donald Dong <xdong(at)csumb(dot)edu> |
---|---|
To: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: How does the planner determine plan_rows ? |
Date: | 2019-01-11 03:56:15 |
Message-ID: | ABF4E76B-B048-4542-9162-EEB044C4F795@csumb.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thank you for the great explanation!
> On Jan 10, 2019, at 7:48 PM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>
>>>>>> "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)
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-01-11 04:01:33 | Re: speeding up planning with partitions |
Previous Message | Andrew Gierth | 2019-01-11 03:48:32 | Re: How does the planner determine plan_rows ? |