Date: Tue, 10 Jul 2012 13:20:06 +0300 (EEST)
From: Pena Kupen <kupen@wippies.fi>
To: pgsql-performance@postgresql.org
Message-ID: <960592203.481341915607581.JavaMail.kupen@wippies.fi>
Subject: Fw: 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

Hello again,

Seems to be ok, by adding normal outer join and some fields on where-part.

Previous, I use to used with Oracle and Sybase databases as much as possibl=
e functions/procedures.
There ware something to do with performance: "Do it on server, not in clien=
t".
Typically all programs were c/s, maybe that or am I missing something?

--
kupen

Maxim Boguk [maxim.boguk@gmail.com] kirjoitti:=20
> On Tue, Jul 10, 2012 at 6:36 PM, Pena Kupen <kupen@wippies.fi> wrote:
>=20
> > 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.
> >
> > My sql is:
> > select count(*)
> >                 from table_h            where                   level <=
=3D
> > get_level_value(11268,id,area) and                     (date1 >=3D '201=
1-1-1'
> > or date2>=3D'2011-1-1') and                  (date1 <=3D '2012-07-09' o=
r
> > date2<=3D'2012-07-09')
> > This takes about 40sek.
> >
> > select count(*)
> >                 from table_h            where                   (date1 =
>=3D
> > '2011-1-1' or date2>=3D'2011-1-1') and                  (date1 <=3D
> > '2012-07-09' or date2<=3D'2012-07-09')
> > when ignoring function, it takes <1sek.
> >
> > Function is:
> > CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area)
> > RETURNS integer
> >    AS $$
> > DECLARE found integer;
> > BEGIN
> >   SELECT 1 INTO found
> >          FROM table_o
> >          WHERE userid=3D_user AND
> >                id=3D_id AND
> >                area=3D_area;
> >   IF (found) THEN
> >      return 3;
> >   ELSE
> >      return 1;
> >   END IF;
> > END;
> > $$
> > LANGUAGE plpgsql;
> >
> > On explain, it seems to me that this function is on filter and it will
> > execute on every row. Total resultset contains 1 700 000 rows.
> > QUERY PLAN
> > Aggregate  (cost=3D285543.89..285543.90 rows=3D1 width=3D0) (actual
> > time=3D32391.380..32391.380 rows=3D1 loops=3D1)
> >  ->  Bitmap Heap Scan on table_h  (cost=3D11017.63..284987.40 rows=3D22=
2596
> > width=3D0) (actual time=3D326.946..31857.145 rows=3D631818 loops=3D1)
> >        Recheck Cond: ((date1 >=3D '2011-01-01'::date) OR (date2 >=3D
> > '2011-01-01'::date))
> >        Filter: (((date1 <=3D '2012-07-09'::date) OR (date2 <=3D
> > '2012-07-09'::date)) AND (level <=3D get_level_value(11268, id, area)))
> >        ->  BitmapOr  (cost=3D11017.63..11017.63 rows=3D669412 width=3D0=
) (actual
> > time=3D321.635..321.635 rows=3D0 loops=3D1)
> >              ->  Bitmap Index Scan on date1  (cost=3D0.00..10626.30
> > rows=3D652457 width=3D0) (actual time=3D84.555..84.555 rows=3D647870 lo=
ops=3D1)
> >                    Index Cond: (date1 >=3D '2011-01-01'::date)
> >              ->  Bitmap Index Scan on date2_table_h  (cost=3D0.00..280.=
03
> > rows=3D16955 width=3D0) (actual time=3D237.074..237.074 rows=3D15222 lo=
ops=3D1)
> >                    Index Cond: (date2 >=3D '2011-01-01'::date)
> >
> > How should I handle this situation and use function?
> >
> >
> You could not have good performance using function in case where direct
> JOIN is only way to have reasonable performance.
> Stop using function and write join with table_o instead, or put whole que=
ry
> with join inside a function.
>=20
> --=20
> Maxim Boguk
> Senior Postgresql DBA
> http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.co=
m/>
>=20
> Phone RU: +7 910 405 4718
> Phone AU: +61 45 218 5678
>=20
> Skype: maxim.boguk
> Jabber: maxim.boguk@gmail.com
> =1C>9=1A@C3: http://mboguk.moikrug.ru/
>=20
> "People problems are solved with people.
> If people cannot solve the problem, try technology.
> People will then wish they'd listened at the first stage."
>=20


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