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