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