update tuple routing and triggers

Поиск
Список
Период
Сортировка
От Amit Langote
Тема update tuple routing and triggers
Дата
Msg-id a94c14a5-073e-43dc-ab6c-9435c4b64340@lab.ntt.co.jp
обсуждение исходный текст
Ответы Re: update tuple routing and triggers
Список pgsql-hackers
Hi.

Fujita-san pointed out in a nearby thread [1] that EXPLAIN ANALYZE shows
duplicate stats for partitions' triggers.

Example:

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);
create table p3 partition of p for values in (3);

create trigger show_data before update on p1 for each row execute
procedure trig_notice_func();
create trigger show_data before update on p2 for each row execute
procedure trig_notice_func();

insert into p values (1), (2);

explain (analyze, costs off, timing off) update p set a = a + 1;
NOTICE:  OLD: (1); NEW: (2)
NOTICE:  OLD: (2); NEW: (3)
                  QUERY PLAN
----------------------------------------------
 Update on p (actual rows=0 loops=1)
   Update on p1
   Update on p2
   Update on p3
   ->  Seq Scan on p1 (actual rows=1 loops=1)
   ->  Seq Scan on p2 (actual rows=1 loops=1)
   ->  Seq Scan on p3 (actual rows=0 loops=1)
 Planning time: 2.000 ms
 Trigger show_data on p1: calls=1
 Trigger show_data on p2: calls=1
 Trigger show_data on p1: calls=1
 Trigger show_data on p2: calls=1
 Execution time: 4.228 ms
(13 rows)

See that the information about the trigger show_data is shown twice for
partitions p1 and p2.  That happens because ExplainPrintTriggers() goes
through both es_result_relations and es_leaf_result_relations to show the
trigger information.  As Fujita-san pointed out in the linked email,
ExecSetupPartitionTupleRouting() adds a partition ResultRelInfo to
es_leaf_result_relations even if it may already have been present in
es_result_relations, which happens if a ResultRelInfo is reused in the
case of update tuple routing.

Attached is a patch to fix that.  After the patch:

explain (analyze, costs off, timing off) update p set a = a + 1;
NOTICE:  OLD: (1); NEW: (2)
NOTICE:  OLD: (2); NEW: (3)
                  QUERY PLAN
----------------------------------------------
 Update on p (actual rows=0 loops=1)
   Update on p1
   Update on p2
   Update on p3
   ->  Seq Scan on p1 (actual rows=1 loops=1)
   ->  Seq Scan on p2 (actual rows=1 loops=1)
   ->  Seq Scan on p3 (actual rows=0 loops=1)
 Planning time: 0.627 ms
 Trigger show_data on p1: calls=1
 Trigger show_data on p2: calls=1
 Execution time: 1.443 ms
(11 rows)

When working on this, I wondered if the es_leaf_result_relations should
actually be named something like es_tuple_routing_result_rels, to denote
the fact that they're created by tuple routing code.  The current name
might lead to someone thinking that it contains *all* leaf result rels,
but that won't remain true after this patch.  Thoughts?

Thanks,
Amit

[1] https://www.postgresql.org/message-id/5A783549.4020409%40lab.ntt.co.jp

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Better Upgrades
Следующее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Better Upgrades