Horton

Thread

Commits

  1. Fix sample output of EXPLAIN ANALYZE.

  2. Fix estimates for ModifyTable paths without RETURNING.

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

    tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com> — 2021-01-20T08:12:34Z

    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

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

    Thomas Munro <thomas.munro@gmail.com> — 2021-01-20T08:20:58Z

    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: https://postgr.es/m/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV%3DqpFJr
R3AcrTS3g%40mail.gmail.com

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

    tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com> — 2021-01-20T08:35:23Z

    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

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2021-01-20T08:42:35Z

    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

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

    Thomas Munro <thomas.munro@gmail.com> — 2021-01-20T09:49:58Z

    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.