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

I have searched solution to my problem a few days. On my query, there is bi=
g performance problem.
It seems to me, that problem is on where-part of sql and it's function.

My sql is:
select count(*)
=09=09from table_h=20
=09=09where=20
=09=09=09level <=3D get_level_value(11268,id,area) and=20
=09=09=09(date1 >=3D '2011-1-1' or date2>=3D'2011-1-1') and=20
=09=09=09(date1 <=3D '2012-07-09' or date2<=3D'2012-07-09')
This takes about 40sek.

select count(*)
=09=09from table_h=20
=09=09where=20
=09=09=09(date1 >=3D '2011-1-1' or date2>=3D'2011-1-1') and=20
=09=09=09(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) RETUR=
NS 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 exec=
ute on every row. Total resultset contains 1 700 000 rows.
QUERY PLAN
Aggregate  (cost=3D285543.89..285543.90 rows=3D1 width=3D0) (actual time=3D=
32391.380..32391.380 rows=3D1 loops=3D1)
  ->  Bitmap Heap Scan on table_h  (cost=3D11017.63..284987.40 rows=3D22259=
6 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-0=
9'::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 loops=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 loops=
=3D1)
                    Index Cond: (date2 >=3D '2011-01-01'::date)

How should I handle this situation and use function?

--
kupen

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