Re: Poor Performance for large queries in functions

John Meinel <[email protected]> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email protected])