Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #431 > unrolled thread

Query slow in function but not in normal query window

Back to article view | Back to comp.databases.postgresql

Contents

  Query slow in function but not in normal query window ivan.sammut@credorax.com - 2013-01-14 03:39 -0800
    Re: Query slow in function but not in normal query window Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2013-01-14 22:10 +0100
    Re: Query slow in function but not in normal query window Ralf Döblitz <doeblitz@doeblitz.net> - 2013-01-14 18:15 +0000
    Re: Query slow in function but not in normal query window Jasen Betts <jasen@xnet.co.nz> - 2013-01-15 07:39 +0000

#431 — Query slow in function but not in normal query window

Hi ppl,

  I have a function which returns a number of transactions which fall between 2 dates and when I run this in a normal query window it is quite fast (40 secs maximum). But when I put the same query in a function and the dates are passed to the query as parameters the function will take more than 4 minutes as if the query is not using an indexing. 

Anyone had the same problem or has any idea?

Thanks
Ivan

[toc] | [next] | [standalone]

#432

ivan.sammut@credorax.com writes:
>   I have a function which returns a number of transactions which fall
> between 2 dates and when I run this in a normal query window it is quite
> fast (40 secs maximum). But when I put the same query in a function and the
> dates are passed to the query as parameters the function will take more than
> 4 minutes as if the query is not using an indexing.
>
> Anyone had the same problem or has any idea?

Try:

 - posting on the psql-general or pgsql-performance mailing list, see
   http://www.postgresql.org/list/ for details

 - using EXECUTE if the function is implemented using PLpgSQL

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#433

ivan.sammut@credorax.com wrote:
> Hi ppl,
> 
>  I have a function which returns a number of transactions which fall between 2 dates and when I run this in a normal query window it is quite fast (40 secs maximum). But when I put the same query in a function and the dates are passed to the query as parameters the function will take more than 4 minutes as if the query is not using an indexing. 
> 
> Anyone had the same problem or has any idea?

Maybe the optimizer could not utilize an index. Try to compare the plans
for both queries (with and without parameters for the dates).

Ralf
-- 
"de.alt.comp.kde Die Kommunistische Desktop Erweiterung."
                  – Sven Paulus in <67hu21$43r$1@imperator.oops.sub.de>

[toc] | [prev] | [next] | [standalone]

#434

On 2013-01-14, ivan.sammut@credorax.com <ivan.sammut@credorax.com> wrote:
> Hi ppl,
>
>   I have a function which returns a number of transactions which fall between 2 dates and when I run this in a normal query window it is quite fast (40 secs maximum). But when I put the same query in a function and the dates are passed to the query as parameters the function will take more than 4 minutes as if the query is not using an indexing. 
>
> Anyone had the same problem or has any idea?

the planner, see "execute"


-- 
⚂⚃ 100% natural

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web