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@xxxxxxxxxxxxxxxxx)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
