Trigger information for auto_explain.

Hello,

Now explain can show trigger statistics (from when?).

=# create table t (a int, b int);
=# create or replace function hoge() returns trigger as 'begin new.b = new.a; return new; end;' language plpgsql;
=# create trigger ins_hoge before insert or update on t for each row execute procedure hoge();

Explain analyze shows trigger information.

=# explain analyze insert into t (select a from generate_series(0, 100) a);
| QUERY PLAN
|----------------------------------------------------------------
| Insert on t (cost=0.00..10.00 rows=1000 width=4) (actual time=2.712.....
| -> Function Scan on generate_series a (cost=0.00..10.00 rows=1000 width=4)
| (actual time=0.047..0.147 rows=101 loops=1)
| Trigger ins_hoge: time=1.881 calls=101
| Total runtime: 3.009 ms

On the other hand, auto_explain doesn't.

=# load auto_explain;
=# set auto_explain.log_min_duration = 0;
=# set auto_explain.log_analyze = 'yes';
=# insert into t (select a from generate_series(0, 100) a);
|LOG: duration: 2.871 ms plan:
| Query Text: insert into t (select a from generate_series(0, 100) a);
| Insert on t (cost=0.00..10.00 rows=1000 width=4)
| -> Function Scan on generate_series a (cost=0.00..10.00 ...

auto_explain will show trigger infos with this patch like this.

=# set auto_explain.log_triggers = 'yes';
=# insert into t (select a from generate_series(0, 100) a);
| LOG: duration: 2.098 ms plan:
| Query Text: insert into t (select a from generate_series(0, 100) a);
| Insert on t (cost=0.00..10.00 rows=1000 width=4) (actual time=2.097..2.097 rows=0 loops=1)
| -> Function Scan on generate_series a (cost=0.00..10.00 rows=1000 width=4) (actual time=0.044..0.123 rows=101 loops=1)
| Trigger ins_hoge: time=1.452 calls=101

This patch consists of two parts,

0001_expose_explain_triggers_v1_20140114.patch

Expose the code to print out trigger information currently
hidden in ExplainOnePlan().

0002_auto_explain_triggers_v1_20140114.patch

Enable auto_explain to output trigger information.

Documentation will be added later..

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Tue, Jan 14, 2014 at 4:25 AM, Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> This patch consists of two parts,
>
> 0001_expose_explain_triggers_v1_20140114.patch
>
> Expose the code to print out trigger information currently
> hidden in ExplainOnePlan().
>
> 0002_auto_explain_triggers_v1_20140114.patch
>
> Enable auto_explain to output trigger information.
>
> Documentation will be added later..
>

Hi,

I think documentation is the only thing that stops this patch to be
commitable... can you add it?

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

Jaime Casanova wrote:
> On Tue, Jan 14, 2014 at 4:25 AM, Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> >
> > This patch consists of two parts,
> >
> > 0001_expose_explain_triggers_v1_20140114.patch

> > 0002_auto_explain_triggers_v1_20140114.patch

> > Documentation will be added later..

> I think documentation is the only thing that stops this patch to be
> commitable... can you add it?

Agreed. I have pushed patch 0001 for now.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello,

> > > This patch consists of two parts,
> > >
> > > 0001_expose_explain_triggers_v1_20140114.patch
>
> > > 0002_auto_explain_triggers_v1_20140114.patch
>
> > > Documentation will be added later..
>
> > I think documentation is the only thing that stops this patch to be
> > commitable... can you add it?
>
> Agreed. I have pushed patch 0001 for now.

Thank you, I'll put it sooner.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hello, I came back with doc patch and revised 0002 patch.

> > > I think documentation is the only thing that stops this patch to be
> > > commitable... can you add it?
> >
> > Agreed. I have pushed patch 0001 for now.
>
> Thank you, I'll put it sooner.

I found the default setting for log_triggers was true in the last
patch while writing doc but it's better be false ragarding
backward compatibility. The 0002 patch attached has been changed
there.

- 0002_auto_explain_triggers_v2_20140122.patch

default value for log_triggers from 'true' to 'false'. As added
documents says.

- 0003_auto_explain_triggers_doc_v1_20140122.patch

documentation.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi, I saw this patch has been moved into "committed patches" but
only the first part (0001_..) for the core is committed as of
32001ab but the rest for extension side seem not to have been
committed.

Would you mind taking a look on that, Álvaro?

regards,

At Wed, 22 Jan 2014 17:28:27 +0900, Kyotaro HORIGUCHI wrote
me> Hello, I came back with doc patch and revised 0002 patch.
me>
me> > > > I think documentation is the only thing that stops this patch to be
me> > > > commitable... can you add it?
me> > >
me> > > Agreed. I have pushed patch 0001 for now.
me> >
me> > Thank you, I'll put it sooner.
me>
me> I found the default setting for log_triggers was true in the last
me> patch while writing doc but it's better be false ragarding
me> backward compatibility. The 0002 patch attached has been changed
me> there.
me>
me> - 0002_auto_explain_triggers_v2_20140122.patch
me>
me> default value for log_triggers from 'true' to 'false'. As added
me> documents says.
me>
me> - 0003_auto_explain_triggers_doc_v1_20140122.patch
me>
me> documentation.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Kyotaro HORIGUCHI wrote:
> Hi, I saw this patch has been moved into "committed patches" but
> only the first part (0001_..) for the core is committed as of
> 32001ab but the rest for extension side seem not to have been
> committed.
>
> Would you mind taking a look on that, Álvaro?

Yep, pushed.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello,

> Kyotaro HORIGUCHI wrote:
> > Hi, I saw this patch has been moved into "committed patches" but
> > only the first part (0001_..) for the core is committed as of
> > 32001ab but the rest for extension side seem not to have been
> > committed.
> >
> > Would you mind taking a look on that, Álvaro?
>
> Yep, pushed.

Thank you.

--
Kyotaro Horiguchi
NTT Open Source Software Center