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?

Albe Laurenz wrote:
> 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

Hello Laurenz,

thank you for your analysis!

Unfortunately your proposal is no option for me, since I do have to
include the WHERE clause in both index and procedure.

By the way: The parameters in the WHERE clause (p_begin,p_until) come
from a RECORD which is filled before via SELECT INTO with begin and end
tablefields of the type date like the following:

my_record RECORD;
...
select into my_record p_begin, p_until
from accounting_interval
where id = 1;

I omitted this information in my first posting to make it easier to
read.

In fact I extracted the bad performing statement to let pgadmin explain
and the same effect shows:

-- Example with bad performance since index isn't used
explain
select c.id_country, sum(c.cost) as sum_cost
from costs c, accounting_interval a
where c.id_user = 123
and c.id_state = 1
and a.id = 1
and date(c.request) between a.p_begin and a.p_until
group by id_country;

-- Example with invoked index (100 times better performance)
explain
select c.id_country, sum(c.cost) as sum_cost
from costs c
where c.id_user = 123
and c.id_state = 1
and date(c.request) between '2007-01-01'::date and '2007-01-31'::date
group by id_country;

Here I cannot see why statement preparation has an effect at all.

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.

So you see me still clueless :O)

Best regards
Rainer Rogatzki