Date: Tue, 10 Jul 2012 12:30:38 +0300 (EEST)
From: Pena Kupen <kupen@wippies.fi>
To: pgsql-performance@postgresql.org
Message-ID: <29875759.2181341912638615.JavaMail.kupen@wippies.fi>
Subject: Re: Custom function in where clause
MIME-Version: 1.0
Content-Type: text/plain; Charset=iso-8859-1; Format=Flowed
Content-Transfer-Encoding: quoted-printable

Hi and thank's guys!

First trying this Brendan's recommendation.

It seems only a small difference between sql and PL/pgSQL. from 40-->37. No=
t so good yet.
I will try Maxim's little later and you all know.

--
kupen

Brendan Jurd [direvus@gmail.com] kirjoitti:=20
> On 10 July 2012 18:36, Pena Kupen <kupen@wippies.fi> wrote:
> > Hi,
> >
> > I have searched solution to my problem a few days. On my query, there i=
s big
> > performance problem.
> > It seems to me, that problem is on where-part of sql and it's function.
> >
>=20
> > How should I handle this situation and use function?
> >
>=20
> I would start by rewriting your function in plain SQL rather than
> PL/pgSQL.  As a general rule, don't write a function in PL/pgSQL
> unless you really need procedurality.  This function does not.
>=20
> For example:
>=20
> CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area)
> RETURNS integer
>    AS $$
>   -- Return 3 if there are matching records in table_o, otherwise return =
1.
>   SELECT CASE WHEN EXISTS (
>          SELECT id
>          FROM table_o
>          WHERE userid=3D_user AND
>                id=3D_id AND
>                area=3D_area
>   ) THEN 3 ELSE 1 END;
> $$
> LANGUAGE sql STABLE;
>=20
> Cheers,
> BJ
>=20


--=20
Wippies-vallankumous on t=E4=E4ll=E4! Varmista paikkasi vallankumouksen etu=
rintamassa ja liity Wippiesiin heti!
http://www.wippies.com/