Skip site navigation (1) Skip section navigation (2)

Re: Trigger function, bad performance

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Rogatzki Rainer *EXTERN*" <rainer(dot)rogatzki(at)ggrz-hagen(dot)nrw(dot)de>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Trigger function, bad performance
Date: 2008-12-05 08:24:10
Message-ID: D960CB61B694CF459DCFB4B0128514C202D671B6@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-performance
Rogatzki Rainer wrote:
> I'm having problems with the following bad performing select-statement
> in a trigger-function (on update before):
> 
>   ...
>   for c in
>     select id_country, sum(cost) as sum_cost
>     from costs
>     where id_user = p_id_user
>     and id_state = 1
>     and date(request) between p_begin and p_until
>     group by id_country;
>   loop
>     ...
>   end loop;
>   ...
> 
> Explain shows that the following existing partial index isn't used:
> 
>   CREATE INDEX ix_costs_user_state_date_0701
>   ON costs
>   USING btree(id_user, id_state, date(request))
>   WHERE id_state = 1 AND date(request) >= '2007-01-01'::date AND
> date(request) <= '2007-01-31'::date;
> 
> 
> The funny thing is, that while executing the statement with 
> type-casted
> string-literals the index is used as expected:
> 
>   ...
>   for c in
>     select id_country, sum(cost) as sum_cost
>     from costs
>     where id_user = p_id_user
>     and id_state = 1
>     and date(request) between '2007-01-01'::date AND 
> '2007-01-31'::date
>     group by id_country;
>   loop
>     ...
>   end loop;
>   ...
> 
> Any ideas?

The problem is that "p_begin" and "p_until" are variables. Consequently PostgreSQL,
when the function is run the first time, will prepare this statement:

    select id_country, sum(cost) as sum_cost
    from costs
    where id_user = $1
    and id_state = 1
    and date(request) between $2 and $3
    group by id_country;

That prepared statement will be reused for subsequent invocations of the trigger
function, whiere the parameters will probably have different values.

So it cannot use the partial index.

If you want the index to be used, don't include "date(request)" in the WHERE clause.

Yours,
Laurenz Albe

In response to

Responses

pgsql-performance by date

Next:From: Rogatzki RainerDate: 2008-12-05 10:41:11
Subject: Re: Trigger function, bad performance
Previous:From: Franck RoutierDate: 2008-12-04 14:08:35
Subject: pg_restore : out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group