Re: Trigger function, bad 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