Trigger function, bad performance

Hello,

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?


Best regards

Rainer Rogatzki (mailto:rainer.rogatzki@ggrz-hagen.nrw.de)