Re: PL/PgSQL Index Usage with Trigger Variables

On Tue, Jan 18, 2005 at 10:53:40AM -0600, Thomas F.O'Connell wrote:

> UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol =
> NEW.myotherbigintcol;
>
> This shows up in the logs with the NEW variable converted to unquoted
> constant data. Is PL/PgSQL smart enough to help the planner figure out
> that it can use an index on mybigintcol based on the fact that the type
> of myotherbigintcol can be discerned from the record of NEW?

Start a new session and try this:

SET debug_print_plan TO on;
SET client_min_messages TO debug1;
<some statement that invokes the trigger>

I did some tests in 7.4.6 and 8.0.0 and an UPDATE statement like
the one above did appear to be using an index from a trigger. Note
that the plan for the UPDATE is logged only the first time it's
executed in a session, presumably because subsequent calls to the
function use a cached plan.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/