From: | "Rogatzki Rainer" <rainer(dot)rogatzki(at)ggrz-hagen(dot)nrw(dot)de> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Trigger function, bad performance |
Date: | 2008-12-05 13:23:33 |
Message-ID: | 595F977C01388944A4B5158975BB676385AC96@sgzhmailbox.ggrz-hagen.nrw.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)rogatzki(at)ggrz-hagen(dot)nrw(dot)de)
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2008-12-05 13:36:47 | Re: Trigger function, bad performance |
Previous Message | A. Kretschmer | 2008-12-05 11:08:02 | Re: Trigger function, bad performance |