Skip site navigation (1) Skip section navigation (2)

Re: PL/pgSQL Loop Vs. Batch Update

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Date: 2006-04-26 01:19:47
Message-ID: 11055.1146014387@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
David Wheeler <david(at)kineticode(dot)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

In response to

Responses

pgsql-performance by date

Next:From: chris smithDate: 2006-04-26 01:39:41
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: markDate: 2006-04-26 01:17:01
Subject: Re: Large (8M) cache vs. dual-core CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group