Re: Trigger function, bad performance

Поиск
Список
Период
Сортировка
От Rogatzki Rainer
Тема Re: Trigger function, bad performance
Дата
Msg-id 595F977C01388944A4B5158975BB676385AC96@sgzhmailbox.ggrz-hagen.nrw.de
обсуждение исходный текст
Ответ на Trigger function, bad performance  ("Rogatzki Rainer" <rainer.rogatzki@ggrz-hagen.nrw.de>)
Ответы Re: Trigger function, bad performance  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список pgsql-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.rogatzki@ggrz-hagen.nrw.de)

В списке pgsql-performance по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Trigger function, bad performance
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Trigger function, bad performance