How to EXPLAIN statements inside a trigger function?

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: How to EXPLAIN statements inside a trigger function?
Date: 2007-12-07 17:26:06
Message-ID: 75341D06-A431-42FC-8EA8-052F01A1ED0B@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm developing some triggers for the first time, and I'm having
trouble analyzing their performance. Does anyone have any advice for
doing EXPLAIN and the like on statements involving NEW? For
instance, I'd like to know what plan PG is coming up with for this
fragment of a trigger function in PLPGSQL:

insert into allSenseRelationPaths (sensePath, weight)
select array[NEW.sense1ID] || p.sensePath, NEW.weight + p.weight
from allSenseRelationPaths as p
where (NEW.sense2ID = p.sensePath[1])
and not NEW.sense1ID = any (sensePath)
and NEW.weight + p.weight > minWeight
and not exists (select 1 from allSenseRelationPaths as EXISTING
where EXISTING.sensepath = array[NEW.sense1ID]
|| p.sensePath)
;

The details of this don't matter much, but there is, of course, a NEW
record, as well as a local variable, minWeight. I can't just
EXPLAIN ... this in psql. I've tried constructing a regular function
as a jig, which sets up the variables, but I can't figure out how to
get EXPLAIN output out of that function. I've seen this:

http://people.planetpostgresql.org/greg/index.php?/archives/106-
Putting-EXPLAIN-results-into-a-table.html

but that seems a bit heavyweight. Surely others have wondered what
plans their triggers are using, how do you do this?

Thanks for any help.

- John Burger
MITRE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2007-12-07 17:26:10 Re: Continual Postgres headaches...
Previous Message Erik Jones 2007-12-07 17:16:04 Re: SQL design pattern for a delta trigger?