Re: Trigger function, bad performance

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