Thread: How to EXPLAIN statements inside a trigger function?
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
"John D. Burger" <john@mitre.org> writes: > 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? The critical thing you're probably running into is that the planner sees such things as parameterized queries, and may not be able to produce plans as good as what it comes up with for queries that have simple constants in place of the parameters. You can investigate what the parameterized plans look like with PREPARE and EXPLAIN EXECUTE. For instance, given your problem query > 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) you'd do something like PREPARE foo(type-of-sense1ID, type-of-sense2ID, type-of-weight, type-of-minWeight) AS select array[$1] || p.sensePath, $3 + p.weight from allSenseRelationPaths as p where ($1 = p.sensePath[1]) and not $1 = any (sensePath) and $3 + p.weight > $4 and not exists (select 1 from allSenseRelationPaths as EXISTING where EXISTING.sensepath = array[$1] || p.sensePath); EXPLAIN [ANALYZE] EXECUTE foo(... some realistic values ...); Note that this isn't specific to trigger functions --- any plpgsql function that does queries will have the same issues. regards, tom lane