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