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

Re: PL/pgSQL Loop Vs. Batch Update

From: David Wheeler <david(at)kineticode(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Date: 2006-04-26 02:27:48
Message-ID: 8FE57896-2F9B-4838-857B-FA6CC2EE162A@kineticode.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-26 02:36:21
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Previous:From: Tom LaneDate: 2006-04-26 02:06:29
Subject: Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)

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