[bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes

Started by tsunakawa.takay@fujitsu.comabout 5 years ago5 messageshackers
Jump to latest
#1tsunakawa.takay@fujitsu.com
tsunakawa.takay@fujitsu.com

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.12 at 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 output rows without RETURNING. Is this a bug or a correct behavior?

Regards
Takayuki Tsunakawa

#2Thomas Munro
thomas.munro@gmail.com
In reply to: tsunakawa.takay@fujitsu.com (#1)
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes

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 output rows 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: /messages/by-id/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV=qpFJr
R3AcrTS3g%40mail.gmail.com

#3tsunakawa.takay@fujitsu.com
tsunakawa.takay@fujitsu.com
In reply to: Thomas Munro (#2)
RE: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes

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 the problem is the sample output in the manual. The fix is attached.

Regards
Takayuki Tsunakawa

Attachments:

0001-Fix-sample-output-of-EXPLAIN-ANALYZE.patchapplication/octet-stream; name=0001-Fix-sample-output-of-EXPLAIN-ANALYZE.patchDownload+2-3
#4Laurenz Albe
laurenz.albe@cybertec.at
In reply to: tsunakawa.takay@fujitsu.com (#3)
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes

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

#5Thomas Munro
thomas.munro@gmail.com
In reply to: Laurenz Albe (#4)
Re: [bug?] EXPLAIN outputs 0 for rows and width in cost estimate for update nodes

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.