Laurenz Albe wrote: > 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 Well it was no 'order' to use a partial index but it was necessary, since the table is vastly filled with log-entries that have to persist over 4 years for documentation. Since old entries are accessed and changed less often we decided to introduce partial indexes for a better performance. This prooved to speed up especially frequently used monthly reports. Now I understand (thanks to Andreas Kretschmer and you) that at execution planning time postgres cannot decide what partial index to use for following procedure calls and thus doesn't invoke it. After implementing Andreas' proposal (execute 'my statement') the expected index is used and performance is 125 time better :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
