X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E076511F657F for ; Tue, 25 Apr 2006 22:20:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75449-03 for ; Tue, 25 Apr 2006 22:19:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 2109811F6557 for ; Tue, 25 Apr 2006 22:19:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3Q1JlVM011056; Tue, 25 Apr 2006 21:19:47 -0400 (EDT) To: David Wheeler cc: pgsql-performance Subject: Re: PL/pgSQL Loop Vs. Batch Update In-reply-to: References: Comments: In-reply-to David Wheeler message dated "Tue, 25 Apr 2006 09:44:55 -0700" Date: Tue, 25 Apr 2006 21:19:47 -0400 Message-ID: <11055.1146014387@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/589 X-Sequence-Number: 18673 David Wheeler 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