Обсуждение: Output affected rows in EXPLAIN

Поиск
Список
Период
Сортировка

Output affected rows in EXPLAIN

От
Damir Belyalov
Дата:
I create a patch that outputs affected rows in EXPLAIN that occur by INSERT/UPDATE/DELETE.
Despite the fact that commands in EXPLAIN ANALYZE query are executed as usual, EXPLAIN doesn't show outputting affected rows as in these commands.
The patch fixes this problem.

Examples:
explain analyze insert into a values (1);
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.076..0.077 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 0.412 ms
(4 rows)

INSERT 0 1

                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Update on a  (cost=0.00..35.50 rows=0 width=0) (actual time=0.059..0.060 rows=0 loops=1)
   ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=10) (actual time=0.012..0.013 rows=7 loops=1)
 Planning Time: 0.142 ms
 Execution Time: 0.666 ms
(4 rows)

UPDATE 7

explain analyze delete from a where n = 1;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Delete on a  (cost=0.00..41.88 rows=0 width=0) (actual time=0.147..0.147 rows=0 loops=1)
   ->  Seq Scan on a  (cost=0.00..41.88 rows=13 width=6) (actual time=0.120..0.123 rows=7 loops=1)
         Filter: (n = 1)
 Planning Time: 1.073 ms
 Execution Time: 0.178 ms
(5 rows)

DELETE 7

EXPLAIN queries without ANALYZE don't affect rows, so the output number is 0.

explain update a set n = 2;
                         QUERY PLAN                        
------------------------------------------------------------
 Update on a  (cost=0.00..35.50 rows=0 width=0)
   ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=10)
(2 rows)

UPDATE 0

Maybe there is no need to add this row when EXPLAIN has no ANALYZE. So it is a discussion question.
Also haven't fixed regress tests yet.

Regards,
Damir Belyalov
Postgres Professional

Вложения

Re: Output affected rows in EXPLAIN

От
Daniel Gustafsson
Дата:
> On 6 Sep 2023, at 14:49, Damir Belyalov <dam.bel07@gmail.com> wrote

> The patch fixes this problem.

Given that EXPLAIN ANALYZE has worked like for a very long time, which problem
is it you have identified?

I'm also not convinced that the added "EXPLAIN" in the below plan is an
improvement in any way.

postgres=# explain (analyze) select * from t;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.064..0.075 rows=5 loops=1)
 Planning Time: 1.639 ms
 Execution Time: 0.215 ms
(3 rows)

EXPLAIN
postgres=#

--
Daniel Gustafsson




Re: Output affected rows in EXPLAIN

От
Tom Lane
Дата:
Damir Belyalov <dam.bel07@gmail.com> writes:
> I create a patch that outputs affected rows in EXPLAIN that occur by
> INSERT/UPDATE/DELETE.
> Despite the fact that commands in EXPLAIN ANALYZE query are executed as
> usual, EXPLAIN doesn't show outputting affected rows as in these commands.
> The patch fixes this problem.

This creates a bug, not fixes one.  It's intentional that "insert into a"
is shown as returning zero rows, because that's what it did.  If you'd
written "insert ... returning", you'd have gotten a different result:

=# explain analyze insert into a values (1);
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.015..0.016 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.015 ms
 Execution Time: 0.027 ms
(4 rows)

=# explain analyze insert into a values (1) returning *;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Insert on a  (cost=0.00..0.01 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
 Planning Time: 0.031 ms
 Execution Time: 0.051 ms
(4 rows)

Now admittedly, if you want to know the number of rows that went to disk,
you have to infer that from the number of rows emitted by the
ModifyTable's child plan.  But that's a matter for documentation
(and I'm pretty sure it's documented someplace).

            regards, tom lane



Re: Output affected rows in EXPLAIN

От
Damir Belyalov
Дата:
This creates a bug, not fixes one.  It's intentional that "insert into a"
is shown as returning zero rows, because that's what it did.  If you'd
written "insert ... returning", you'd have gotten a different result:
Maybe I didn't understand you correctly, but I didn't touch the number of affected rows in EXPLAIN output.
It's just a simple patch that adds 1 row after using commands: EXPLAIN INSERT, EXPLAIN UPDATE, EXPLAIN DELETE.
It was done because the commands INSERT/UPDATE/DELETE return one row after execution: "UPDATE 7" or "INSERT 0 4".
EXPLAIN (ANALYZE) INSERT/UPDATE/DELETE does the same thing as these commands, but doesn't output this row. So I added it.


Patch is fixed. There is no row "EXPLAIN" in queries like:
postgres=# explain (analyze) select * from t;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.064..0.075 rows=5 loops=1)
 Planning Time: 1.639 ms
 Execution Time: 0.215 ms
(3 rows)

EXPLAIN


What is about queries EXPLAIN INSERT/UPDATE/DELETE without ANALYZE?
Now it is outputting a row with 0 affected (inserted) rows at the end: "INSERT 0 0", "UPDATE 0". Example:
explain update a set n = 2;
                         QUERY PLAN                        
------------------------------------------------------------
 Update on a  (cost=0.00..35.50 rows=0 width=0)
   ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=10)
(2 rows)

UPDATE 0

Regards,
Damir Belyalov
Postgres Professional
Вложения

Re: Output affected rows in EXPLAIN

От
kuroda.keisuke@nttcom.co.jp
Дата:
Hi hackers,

Indeed, I think it is a little confusing that when executing
EXPLAIN(ANALYZE), even though an update is actually occurring,
the commandtag of the update result is not returned.

However, the manual also describes the information that will be
affected when EXPLAIN (ANALYZE) is executed as important information.
https://www.postgresql.org/docs/current/sql-explain.html

Also, in most cases, users who use EXPLAIN(ANALYZE) only want
an execution plan of a statement.
If command tags are not required, this can be controlled using
the QUIET variable, but command tags other than EXPLAIN will also
be omitted, increasing the scope of the effect.
We can check the number of updated rows from execute plan,
I think there is no need to return the command tag
when EXPLAIN(ANALYZE) is executed by default.

## patch and QUIET=off(default)

postgres=# explain (analyze) insert into a values (1);
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.227..0.228 
rows=0 loops=1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.013..0.015 rows=1 loops=1)
  Planning Time: 0.152 ms
  Execution Time: 0.480 ms
(4 rows)

INSERT 0 1

## patch and QUIET=on(psql work quietly)

'INSERT 0 1' is omitted both 'explain(analyze) and 'INSERT'.

postgres=# \set QUIET on
postgres=# explain (analyze) insert into a values (1);
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  Insert on a  (cost=0.00..0.01 rows=0 width=0) (actual time=0.058..0.059 
rows=0 loops=1)
    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.004..0.005 rows=1 loops=1)
  Planning Time: 0.059 ms
  Execution Time: 0.117 ms
(4 rows)

postgres=# insert into a values (1);
postgres=#

Best Regards,
Keisuke Kuroda
NTT COMWARE

On 2023-09-07 23:57, Damir Belyalov wrote:
>> This creates a bug, not fixes one.  It's intentional that "insert
>> into a"
>> is shown as returning zero rows, because that's what it did.  If
>> you'd
>> written "insert ... returning", you'd have gotten a different
>> result:
> 
> Maybe I didn't understand you correctly, but I didn't touch the number
> of affected rows in EXPLAIN output.
> It's just a simple patch that adds 1 row after using commands: EXPLAIN
> INSERT, EXPLAIN UPDATE, EXPLAIN DELETE.
> It was done because the commands INSERT/UPDATE/DELETE return one row
> after execution: "UPDATE 7" or "INSERT 0 4".
> EXPLAIN (ANALYZE) INSERT/UPDATE/DELETE does the same thing as these
> commands, but doesn't output this row. So I added it.
> 
> Patch is fixed. There is no row "EXPLAIN" in queries like:
> 
> postgres=# explain (analyze) select * from t;
>                                           QUERY PLAN
> -----------------------------------------------------------------------------------------------
>  Seq Scan on t  (cost=0.00..35.50 rows=2550 width=4) (actual
> time=0.064..0.075 rows=5 loops=1)
>  Planning Time: 1.639 ms
>  Execution Time: 0.215 ms
> (3 rows)
> 
>  EXPLAIN
> 
> What is about queries EXPLAIN INSERT/UPDATE/DELETE without ANALYZE?
> Now it is outputting a row with 0 affected (inserted) rows at the end:
> "INSERT 0 0", "UPDATE 0". Example:
> explain update a set n = 2;
>                          QUERY PLAN
> ------------------------------------------------------------
>  Update on a  (cost=0.00..35.50 rows=0 width=0)
>    ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=10)
> (2 rows)
> 
> UPDATE 0
> 
> Regards,
> Damir Belyalov
> Postgres Professional




Re: Output affected rows in EXPLAIN

От
John Naylor
Дата:
On Wed, Nov 15, 2023 at 2:17 PM <kuroda.keisuke@nttcom.co.jp> wrote:
>
> We can check the number of updated rows from execute plan,
> I think there is no need to return the command tag
> when EXPLAIN(ANALYZE) is executed by default.

Given that several people have voiced an opinion against this patch,
I'm marking it rejected.