Re: Trigger function, bad performance

Rogatzki Rainer wrote:
> > > 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 problem is that "p_begin" and "p_until" are variables.
> >
> > So it cannot use the partial index.
> >
> > If you want the index to be used, don't include "date(request)" in the
> > WHERE clause.
>
> Unfortunately your proposal is no option for me, since I do have to
> include the WHERE clause in both index and procedure.

You have been ordered to use a partial index?

> Apart from this I don't really understand why statement preparation
> combined with parameters in functions prevent index invocation.
> Especially since p_id_user is a parameter as well which doesn't prevent
> the usage of another existing index on costs.id_user and costs.id_state.

The connection with parameters is by chance.

The main thing is that both "p_begin" and "p_until" are variables.

Andreas Kretschmer gave you the advice you'll want: use dynamic SQL.

Yours,
Laurenz Albe