Re: Trigger function, bad performance

> > Rogatzki wrote:
> > -- 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.
>
> Andreas Kretschmer wrote:
> The planner don't know the parameters at compile-time. Because of this
fakt, the planner choose a other plan (a seq-scan).
>
> You can try to use execute 'your query'. In this case the planner
investigate a new plan, and (maybe) with the index.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello Andreas,

your hint did the trick - thank you very much!

After using "execute 'my query'" the index is used as expected.

Though I still wonder why the poor performance occurred since november,
without having done any relevant changes to neither postgres nor db
model (including index, procedures, ...) as far as I know.

Anyway - I'm deeply content with your solution.

Best regards

Rainer Rogatzki (mailto:rainer(dot)rogatzki(at)ggrz-hagen(dot)nrw(dot)de)