Re: Trigger function, bad performance - Mailing list 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?

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