Custom function in where clause

От: Pena Kupen
Тема: Custom function in where clause
Дата: ,
Msg-id: 203599184.371341909375800.JavaMail.kupen@wippies.fi
(см: обсуждение, исходный текст)
Ответы: Re: Custom function in where clause  (Maxim Boguk)
Re: Custom function in where clause  (Brendan Jurd)
Список: pgsql-performance

Скрыть дерево обсуждения

Custom function in where clause  (Pena Kupen, )
 Re: Custom function in where clause  (Maxim Boguk, )
 Re: Custom function in where clause  (Brendan Jurd, )
 Re: Custom function in where clause  (Pena Kupen, )

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
700000 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=631818loops=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=647870loops=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.074rows=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/



В списке pgsql-performance по дате сообщения:

От: Andres Freund
Дата:
Сообщение: Re: Massive I/O spikes during checkpoint
От: Merlin Moncure
Дата:
Сообщение: Re: The need for clustered indexes to boost TPC-V performance