Re: Trigger function, bad performance

am  Fri, dem 05.12.2008, um 11:41:11 +0100 mailte Rogatzki Rainer folgendes:
>   -- 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.

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