Re: Output affected rows in EXPLAIN
От | kuroda.keisuke@nttcom.co.jp |
---|---|
Тема | Re: Output affected rows in EXPLAIN |
Дата | |
Msg-id | d0aef37c33e99839bc586550b1659747@nttcom.co.jp обсуждение исходный текст |
Ответ на | Re: Output affected rows in EXPLAIN (Damir Belyalov <dam.bel07@gmail.com>) |
Ответы |
Re: Output affected rows in EXPLAIN
(John Naylor <johncnaylorls@gmail.com>)
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Peter SmithДата:
Сообщение: Re: add log messages when replication slots become active and inactive (was Re: Is it worth adding ReplicationSlot active_pid to ReplicationSlotPersistentData?)