Re: PL/pgSQL Loop Vs. Batch Update - Mailing list pgsql-performance

From Tom Lane
Subject Re: PL/pgSQL Loop Vs. Batch Update
Date
Msg-id 11055.1146014387@sss.pgh.pa.us
Whole thread Raw
In response to PL/pgSQL Loop Vs. Batch Update  (David Wheeler <david@kineticode.com>)
Responses Re: PL/pgSQL Loop Vs. Batch Update  (David Wheeler <david@kineticode.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: mark@mark.mielke.cc
Date:
Subject: Re: Large (8M) cache vs. dual-core CPUs
Next
From: "chris smith"
Date:
Subject: Re: Query on postgresql 7.4.2 not using index