From: Craig Ringer <craig@postnewspapers.com.au>
To: Tyler Hildebrandt <tyler@campbell-lange.net>
Cc: pgsql-performance@postgresql.org, Sebastian Ritter <sebastian@campbell-lange.net>
Cc: rory <rory@campbell-lange.net>
Subject: Re: Query timing increased from 3s to 55s when used as function instead of select
Date: Thu, 27 May 2010 23:33:06 +0800
Message-ID: <4BFE90B2.2050604@postnewspapers.com.au> (raw)
In-Reply-To: <20100521135415.GA3445@campbell-lange.net>
References: <20100521135415.GA3445@campbell-lange.net>
On 21/05/2010 9:54 PM, Tyler Hildebrandt wrote:
> We're using a function that when run as a select statement outside of the
> function takes roughly 1.5s to complete whereas running an identical
> query within a function is taking around 55s to complete.
>
> We are lost as to why placing this query within a function as opposed to
> substituting the variables in a select statement is so drastically different.
This is a frequently asked question. It's the same issue as with
prepared queries, where the planner has to pick a more general plan when
it doesn't know the value of a parameter. The short answer is "work
around it by using EXECUTE ... USING to invoke your query dynamically".
( Oddly, this FAQ doesn't seem to be on the FAQ list at
http://wiki.postgresql.org/wiki/FAQ )
--
Craig Ringer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, tyler@campbell-lange.net, sebastian@campbell-lange.net, rory@campbell-lange.net
Subject: Re: Query timing increased from 3s to 55s when used as function instead of select
In-Reply-To: <4BFE90B2.2050604@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox