Re: Poor Performance for large queries in functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Meinel <john(at)johnmeinel(dot)com>
Cc: pgsql-hackers-win32 <pgsql-hackers-win32(at)postgresql(dot)org>
Subject: Re: Poor Performance for large queries in functions
Date: 2004-09-29 05:10:47
Message-ID: 22682.1096434647@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers-win32 pgsql-performance

John Meinel <john(at)johnmeinel(dot)com> writes:
> ... However, if I try to
> bundle this query up into a server side function, it runs very slow (10
> seconds). I'm trying to figure out why, but since I can't run EXPLAIN
> ANALYZE inside a function, I don't really know what else to do.

A parameterized query inside a function is basically the same as a
PREPARE'd query with parameters at the SQL level. So you can
investigate what's happening here with

PREPARE foo(int) AS
SELECT * FROM object WHERE id in (
SELECT id FROM data_t WHERE project_id = $1
UNION SELECT ... ;

EXPLAIN ANALYZE EXECUTE foo(48542);

I'm not sure where the problem is either, so please do send along the
results.

regards, tom lane

PS: pgsql-performance would be a more appropriate venue for this
discussion.

In response to

Responses

Browse pgsql-hackers-win32 by date

  From Date Subject
Next Message John Meinel 2004-09-29 06:34:07 Re: Poor Performance for large queries in functions
Previous Message John Meinel 2004-09-29 03:14:23 Poor Performance for large queries in functions

Browse pgsql-performance by date

  From Date Subject
Next Message John Meinel 2004-09-29 06:34:07 Re: Poor Performance for large queries in functions
Previous Message Robert Creager 2004-09-29 03:51:56 Re: This query is still running after 10 hours...