From: | Pena Kupen <kupen(at)wippies(dot)fi> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Custom function in where clause |
Date: | 2012-07-10 08:36:15 |
Message-ID: | 203599184.371341909375800.JavaMail.kupen@wippies.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
My sql is:
select count(*)
from table_h
where
level <= get_level_value(11268,id,area) and
(date1 >= '2011-1-1' or date2>='2011-1-1') and
(date1 <= '2012-07-09' or date2<='2012-07-09')
This takes about 40sek.
select count(*)
from table_h
where
(date1 >= '2011-1-1' or date2>='2011-1-1') and
(date1 <= '2012-07-09' or date2<='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=_user AND
id=_id AND
area=_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=285543.89..285543.90 rows=1 width=0) (actual time=32391.380..32391.380 rows=1 loops=1)
-> Bitmap Heap Scan on table_h (cost=11017.63..284987.40 rows=222596 width=0) (actual time=326.946..31857.145 rows=631818 loops=1)
Recheck Cond: ((date1 >= '2011-01-01'::date) OR (date2 >= '2011-01-01'::date))
Filter: (((date1 <= '2012-07-09'::date) OR (date2 <= '2012-07-09'::date)) AND (level <= get_level_value(11268, id, area)))
-> BitmapOr (cost=11017.63..11017.63 rows=669412 width=0) (actual time=321.635..321.635 rows=0 loops=1)
-> Bitmap Index Scan on date1 (cost=0.00..10626.30 rows=652457 width=0) (actual time=84.555..84.555 rows=647870 loops=1)
Index Cond: (date1 >= '2011-01-01'::date)
-> Bitmap Index Scan on date2_table_h (cost=0.00..280.03 rows=16955 width=0) (actual time=237.074..237.074 rows=15222 loops=1)
Index Cond: (date2 >= '2011-01-01'::date)
How should I handle this situation and use function?
--
kupen
--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Maxim Boguk | 2012-07-10 08:44:18 | Re: Custom function in where clause |
Previous Message | Sreejith Balakrishnan | 2012-07-10 08:21:06 | Any tool/script available which can be used to measure scalability of an application's database. |