Re: How to improve: performance of query on postgresql 8.3 takes days

On 29 Jul 2010, at 23:58, Dino Vliet wrote:

> CREATE OR REPLACE FUNCTION agenttype1(a character)
> RETURNS integer AS

> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
>
> CREATE OR REPLACE FUNCTION agenttype2(a character)
> RETURNS integer AS

> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
>
> CREATE OR REPLACE FUNCTION agenttype3(a character)
> RETURNS integer AS

> LANGUAGE 'plpgsql' VOLATILE
> COST 100;

As others have already said, using these functions will be less efficient than using joins.

Regardless of that though, you should at least declare these functions as STABLE instead of VOLATILE, see:

http://www.postgresql.org/docs/8.4/interactive/xfunc-volatility.html

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4c52ae01286211819977167!