pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCustom function in where clause
5+ messages / 3 participants
[nested] [flat]
* Custom function in where clause
@ 2012-07-10 08:36 Pena Kupen <kupen@wippies.fi>
0 siblings, 2 replies; 5+ messages in thread
From: Pena Kupen @ 2012-07-10 08:36 UTC (permalink / raw)
To: 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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Custom function in where clause
@ 2012-07-10 08:44 Maxim Boguk <maxim.boguk@gmail.com>
parent: Pena Kupen <kupen@wippies.fi>
1 sibling, 0 replies; 5+ messages in thread
From: Maxim Boguk @ 2012-07-10 08:44 UTC (permalink / raw)
To: Pena Kupen <kupen@wippies.fi>; +Cc: pgsql-performance
On Tue, Jul 10, 2012 at 6:36 PM, 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.
>
> 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?
>
>
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 query
with join inside a function.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/;
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"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."
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Custom function in where clause
@ 2012-07-10 08:45 Brendan Jurd <direvus@gmail.com>
parent: Pena Kupen <kupen@wippies.fi>
1 sibling, 0 replies; 5+ messages in thread
From: Brendan Jurd @ 2012-07-10 08:45 UTC (permalink / raw)
To: Pena Kupen <kupen@wippies.fi>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Custom function in where clause
@ 2012-07-10 09:30 Pena Kupen <kupen@wippies.fi>
0 siblings, 0 replies; 5+ messages in thread
From: Pena Kupen @ 2012-07-10 09:30 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Fw: Re: Custom function in where clause
@ 2012-07-10 10:20 Pena Kupen <kupen@wippies.fi>
0 siblings, 0 replies; 5+ messages in thread
From: Pena Kupen @ 2012-07-10 10:20 UTC (permalink / raw)
To: pgsql-performance
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 possible functions/procedures.
There ware something to do with performance: "Do it on server, not in client".
Typically all programs were c/s, maybe that or am I missing something?
--
kupen
Maxim Boguk [maxim.boguk@gmail.com] kirjoitti:
> On Tue, Jul 10, 2012 at 6:36 PM, 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.
> >
> > 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?
> >
> >
> 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 query
> with join inside a function.
>
> --
> Maxim Boguk
> Senior Postgresql DBA
> http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/;
>
> Phone RU: +7 910 405 4718
> Phone AU: +61 45 218 5678
>
> Skype: maxim.boguk
> Jabber: maxim.boguk@gmail.com
> >9@C3: http://mboguk.moikrug.ru/
>
> "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."
>
--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-07-10 10:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-10 08:36 Custom function in where clause Pena Kupen <kupen@wippies.fi>
2012-07-10 08:44 ` Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 08:45 ` Brendan Jurd <direvus@gmail.com>
2012-07-10 09:30 Re: Custom function in where clause Pena Kupen <kupen@wippies.fi>
2012-07-10 10:20 Fw: Re: Custom function in where clause Pena Kupen <kupen@wippies.fi>
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