Re: PL/pgSQL Loop Vs. Batch Update

David Wheeler <david@kineticode.com> writes:
> This post is longish and has a bit of code, but here's my question up-
> front: Why are batch queries in my PL/pgSQL functions no faster than
> iterating over a loop and executing a series of queries for each
> iteration of the loop?

You'd really have to look at the plans generated for each of the
commands in the functions to be sure.  A knee-jerk reaction is to
suggest that that NOT IN might be the core of the problem, but it's
only a guess.

It's a bit tricky to examine the behavior of a parameterized query,
which is what these will all be since they depend on local variables
of the plpgsql function (which are passed as parameters to the main
SQL executor).  The basic idea is

    PREPARE foo(datatype, datatype, ...) AS SELECT ... $1 ... $2 ...

    EXPLAIN ANALYZE EXECUTE foo(value, value)

            regards, tom lane