In-Reply-To: <11055.1146014387@sss.pgh.pa.us>
References: <FFF901A7-232A-4140-AE41-89ABE7766A85@kineticode.com>
	<11055.1146014387@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v749.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <8FE57896-2F9B-4838-857B-FA6CC2EE162A@kineticode.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: David Wheeler <david@kineticode.com>
Subject: Re: PL/pgSQL Loop Vs. Batch Update 
Date: Tue, 25 Apr 2006 19:27:48 -0700
To: Tom Lane <tgl@sss.pgh.pa.us>

On Apr 25, 2006, at 18:19, Tom Lane wrote:

> 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.

Well, the rows are indexed (I forgot to include the indexes in my  
first post), and given that each entry_id has no more than ten  
associated tag_ids, I would expect it to be quite fast, relying on  
the primary key index to look up the entry_id first, and then the  
associated tag_ids. But that's just a guess on my part, too. Perhaps  
I should try a left outer join with tag_id IS NULL?

> 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).

Right, that makes sense.

> The basic idea is
>
> 	PREPARE foo(datatype, datatype, ...) AS SELECT ... $1 ... $2 ...
>
> 	EXPLAIN ANALYZE EXECUTE foo(value, value)

Just on a lark, I tried to get this to work:

try=# explain analyze EXECUTE foo(1, ARRAY 
[600001,600002,600003,600004,600005,600006,600007]);
                                       QUERY PLAN
------------------------------------------------------------------------ 
--------------
Result  (cost=0.00..0.01 rows=1 width=0) (actual time=26.241..26.251  
rows=1 loops=1)
Total runtime: 27.512 ms
(2 rows)

That's not much use. Is there no way to EXPLAIN ANALYZE this stuff?

Thanks Tom.

Best,

David