Output affected rows in EXPLAIN - Mailing list pgsql-hackers

From Damir Belyalov
Subject Output affected rows in EXPLAIN
Date
Msg-id CALH1LgtBTFoVg7H20sQG5cc7ytzXTFwsxdV4gNt4gptzG9H7=g@mail.gmail.com
Whole thread Raw
Responses Re: Output affected rows in EXPLAIN
Re: Output affected rows in EXPLAIN
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Andy Fan
Date:
Subject: make add_paths_to_append_rel aware of startup cost
Next
From: Robert Haas
Date:
Subject: Re: A minor adjustment to get_cheapest_path_for_pathkeys