From: Pena Kupen <kupen@wippies.fi>
To: pgsql-performance@postgresql.org
Subject: Re: Custom function in where clause
Date: Tue, 10 Jul 2012 12:30:38 +0300 (EEST)
Message-ID: <29875759.2181341912638615.JavaMail.kupen@wippies.fi> (raw)
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. Not so good yet.
I will try Maxim's little later and you all know.
--
kupen
Brendan Jurd [direvus@gmail.com] kirjoitti:
> 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 is big
> > performance problem.
> > It seems to me, that problem is on where-part of sql and it's function.
> >
>
> > How should I handle this situation and use function?
> >
>
> 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.
>
> For example:
>
> 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=_user AND
> id=_id AND
> area=_area
> ) THEN 3 ELSE 1 END;
> $$
> LANGUAGE sql STABLE;
>
> Cheers,
> BJ
>
--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kupen@wippies.fi
Subject: Re: Custom function in where clause
In-Reply-To: <29875759.2181341912638615.JavaMail.kupen@wippies.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox