From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Stats for triggers on partitioned tables not shown in EXPLAIN ANALYZE |
Date: | 2017-08-15 12:20:10 |
Message-ID: | 57163e18-8e56-da83-337a-22f2c0008051@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
I noticed that runtime stats for BEFORE ROW INSERT triggers on leaf
partitions of partitioned tables aren't reported in EXPLAIN ANALYZE.
Here is an example:
postgres=# create table trigger_test (a int, b text) partition by list (a);
CREATE TABLE
postgres=# create table trigger_test1 partition of trigger_test for
values in (1);
CREATE TABLE
postgres=# create trigger before_ins_row_trig BEFORE INSERT ON
trigger_test1 FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER
postgres=# create trigger after_ins_row_trig AFTER INSERT ON
trigger_test1 FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER
postgres=# explain analyze insert into trigger_test values (1, 'foo');
NOTICE: before_ins_row_trig() BEFORE ROW INSERT ON trigger_test1
NOTICE: NEW: (1,foo)
NOTICE: after_ins_row_trig() AFTER ROW INSERT ON trigger_test1
NOTICE: NEW: (1,foo)
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Insert on trigger_test (cost=0.00..0.01 rows=1 width=36) (actual
time=0.193..0.193 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=36) (actual
time=0.002..0.003 rows=1 loops=1)
Planning time: 0.027 ms
Trigger after_ins_row_trig on trigger_test1: time=0.075 calls=1
Execution time: 0.310 ms
(5 rows)
where trig_data() is borrowed from the regression test in postgres_fdw.
The stats for the AFTER ROW INSERT trigger after_ins_row_trig are well
shown in the output, but the stats for the BEFORE ROW INSERT trigger
before_ins_row_trig aren't at all. I think we should show the latter as
well.
Another thing I noticed is: runtime stats for BEFORE STATEMENT
UPDATE/DELETE triggers on partitioned table roots aren't reported in
EXPLAIN ANALYZE, either, as shown in a below example:
postgres=# create trigger before_upd_stmt_trig BEFORE UPDATE ON
trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
postgres=# create trigger after_upd_stmt_trig AFTER UPDATE ON
trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
postgres=# explain analyze update trigger_test set b = 'bar' where a = 1;
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = BEFORE,
level = STATEMENT
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER,
level = STATEMENT
QUERY PLAN
----------------------------------------------------------------------------------------------------------
-----
Update on trigger_test (cost=0.00..25.88 rows=6 width=42) (actual
time=0.296..0.296 rows=0 loops=1)
Update on trigger_test1
-> Seq Scan on trigger_test1 (cost=0.00..25.88 rows=6 width=42)
(actual time=0.010..0.011 rows=1 loops=1)
Filter: (a = 1)
Planning time: 0.152 ms
Trigger after_upd_stmt_trig on trigger_test: time=0.141 calls=1
Execution time: 0.476 ms
(7 rows)
where trigger_func() is borrowed from the regression test, too. The
stats for the BEFORE STATEMENT UPDATE trigger before_upd_stmt_trig
aren't shown at all in the output. I think this should also be fixed.
So here is a patch for fixing both issues. Changes I made are:
* To fix the former, I added a new List member es_leaf_result_relations
to EState, modified ExecSetupPartitionTupleRouting so that it creates
ResultRelInfos with the EState's es_instrument and then saves them in
that list, and modified ExplainPrintTriggers to show stats for BEFORE
ROW INSERT triggers on leaf partitions (if any) by looking at that list.
I also modified copy.c so that ExecSetupPartitionTupleRouting and
related things are performed in CopyFrom after its EState creation.
* To fix the latter, I modified ExplainPrintTriggers to show stats for
BEFORE STATEMENT UPDATE/DELETE triggers on partitioned table roots (if
any) by looking at the es_root_result_relations array.
* While fixing these, I noticed that AFTER ROW INSERT triggers on leaf
partitions and BEFORE STATEMENT UPDATE/DELETE triggers on partitioned
table roots re-open relations and re-create ResultRelInfos (trigger-only
ResultRelInfos!) in ExecGetTriggerResultRel when executing triggers (and
that in the above examples, the stats for AFTER ROW INSERT trigger/AFTER
STATEMENT UPDATE trigger are shown the result for
es_trig_target_relations in ExplainPrintTriggers). But that wouldn't be
efficient (and EXPLAIN ANALYZE might produce odd outputs), so I modified
ExecGetTriggerResultRel so that it searches es_leaf_result_relations and
es_root_result_relations in addition to es_result_relations.
Best regards,
Etsuro Fujita
Attachment | Content-Type | Size |
---|---|---|
explain-trigger-stats-for-partitioned-tables.patch | text/plain | 12.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-08-15 13:20:41 | Re: [BUGS] [postgresql 10 beta3] unrecognized node type: 90 |
Previous Message | Amit Kapila | 2017-08-15 11:31:44 | Re: [postgresql 10 beta3] unrecognized node type: 90 |