From: | Damir Belyalov <dam(dot)bel07(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>, a(dot)lepikhov(at)postgrespro(dot)ru, Daniel Gustafsson <dgustafsson(at)postgresql(dot)org> |
Subject: | Output affected rows in EXPLAIN |
Date: | 2023-09-06 12:49:36 |
Message-ID: | CALH1LgtBTFoVg7H20sQG5cc7ytzXTFwsxdV4gNt4gptzG9H7=g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | Content-Type | Size |
---|---|---|
0001-Output-affected-rows-in-EXPLAIN.patch | text/x-patch | 7.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2023-09-06 12:50:30 | Re: A minor adjustment to get_cheapest_path_for_pathkeys |
Previous Message | Andy Fan | 2023-09-06 12:39:56 | make add_paths_to_append_rel aware of startup cost |