Thread: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
[bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
From
"tsunakawa.takay@fujitsu.com"
Date:
Hello, While I'm investigating problems with parallel DML on another thread, I encountered a fishy behavior of EXPLAIN on HEAD. Is this a bug? As follows, the rows and width values of Update node is 0. These were 1 and 10 respectively in versions 9.4.26 and 10.12at hand. postgres=# create table a (c int); CREATE TABLE postgres=# insert into a values(1); INSERT 0 1 postgres=# analyze a; ANALYZE postgres=# begin; BEGIN postgres=*# explain analyze update a set c=2; QUERY PLAN -------------------------------------------------------------------------------------------------- Update on a (cost=0.00..1.01 rows=0 width=0) (actual time=0.189..0.191 rows=0 loops=1) -> Seq Scan on a (cost=0.00..1.01 rows=1 width=10) (actual time=0.076..0.079 rows=1 loops=1) Planning Time: 0.688 ms Execution Time: 0.494 ms (4 rows) With RETURNING, the values are not 0 as follows. postgres=*# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# explain analyze update a set c=2 returning *; QUERY PLAN -------------------------------------------------------------------------------------------------- Update on a (cost=0.00..1.01 rows=1 width=10) (actual time=0.271..0.278 rows=1 loops=1) -> Seq Scan on a (cost=0.00..1.01 rows=1 width=10) (actual time=0.080..0.082 rows=1 loops=1) Planning Time: 0.308 ms Execution Time: 0.392 ms (4 rows) The above holds true for Insert and Delete nodes as well. In the manual, they are not 0. https://www.postgresql.org/docs/devel/using-explain.html -------------------------------------------------- EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Update on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1) -> Bitmap Heap Scan on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1) ... -------------------------------------------------- This behavior may possibly be considered as an intended behavior for the reason that Update/Insert/Delete nodes don't outputrows without RETURNING. Is this a bug or a correct behavior? Regards Takayuki Tsunakawa
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
From
Thomas Munro
Date:
On Wed, Jan 20, 2021 at 9:12 PM tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com> wrote: > This behavior may possibly be considered as an intended behavior for the reason that Update/Insert/Delete nodes don't outputrows without RETURNING. Is this a bug or a correct behavior? Hi Tsunakawa-san, This was a change made deliberately. Do you see a problem? commit f0f13a3a08b2757997410f3a1c38bdc22973c525 Author: Thomas Munro <tmunro@postgresql.org> Date: Mon Oct 12 20:41:16 2020 +1300 Fix estimates for ModifyTable paths without RETURNING. In the past, we always estimated that a ModifyTable node would emit the same number of rows as its subpaths. Without a RETURNING clause, the correct estimate is zero. Fix, in preparation for a proposed parallel write patch that is sensitive to that number. A remaining problem is that for RETURNING queries, the estimated width is based on subpath output rather than the RETURNING tlist. Reviewed-by: Greg Nancarrow <gregn4422@gmail.com> Discussion: https://postgr.es/m/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV%3DqpFJr R3AcrTS3g%40mail.gmail.com
RE: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
From
"tsunakawa.takay@fujitsu.com"
Date:
Hi Thomas-san, From: Thomas Munro <thomas.munro@gmail.com> > This was a change made deliberately. Do you see a problem? Thank you, I was surprised at your very quick response. I just wanted to confirm I can believe EXPLAIN output. Then theproblem is the sample output in the manual. The fix is attached. Regards Takayuki Tsunakawa
Attachment
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
From
Laurenz Albe
Date:
On Wed, 2021-01-20 at 08:35 +0000, tsunakawa.takay@fujitsu.com wrote: > > This was a change made deliberately. Do you see a problem? > > Thank you, I was surprised at your very quick response. > I just wanted to confirm I can believe EXPLAIN output. > Then the problem is the sample output in the manual. > The fix is attached. +1. That was obviously an oversight. Yours, Laurenz Albe
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes
From
Thomas Munro
Date:
On Wed, Jan 20, 2021 at 9:42 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > On Wed, 2021-01-20 at 08:35 +0000, tsunakawa.takay@fujitsu.com wrote: > > > This was a change made deliberately. Do you see a problem? > > > > Thank you, I was surprised at your very quick response. > > I just wanted to confirm I can believe EXPLAIN output. > > Then the problem is the sample output in the manual. > > The fix is attached. > > +1. That was obviously an oversight. Pushed. Thanks.