DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=hXHxdIZdDvh9RC8jnFWN49p99GxYz3oV8FEO2YRvB7RQHZkt+yXIinorg9bkF0gnni22fIqZELjjokwV0rdBAbRL3zJMAN3z/cAlfQViTG6UlixwkCv8UrX6q0EaTciR6RChszi8X68Nzded/ZVMvqsNJAoQSVPpn22HP6Np8is=
Message-ID: <dcc563d10805211144i592bd400m43238c5def8eae66@mail.gmail.com>
Date: Wed, 21 May 2008 12:44:44 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Frank Dekervel" <frank.dekervel@smartlounge.be>
Subject: Re: "append" takes a lot of time in a query
Cc: pgsql-performance@postgresql.org
In-Reply-To: <200805211237.23178.frank.dekervel@smartlounge.be>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200805091009.46893.frank.dekervel@smartlounge.be>
	<13354.1210342366@sss.pgh.pa.us>
	<200805161759.38066.frank.dekervel@smartlounge.be>
	<200805211237.23178.frank.dekervel@smartlounge.be>

I think you're looking to return set of record or something like that.

On Wed, May 21, 2008 at 4:37 AM, Frank Dekervel
<frank.dekervel@smartlounge.be> wrote:
> Hello,
>
> Small update on this problem:
>
> Wouter Verhelst came to help debugging, and he determined that the 2 seconds
> were spent planning the query and not executing the query. (executing the
> query is quick as seen in the query plan).
>
> To avoid replanning this query all the time, Wouter suggest replacing the
> query with a stored procedure (so that the query plan was saved). We did a
> proof of concept, and it seemed to work very well.
>
> Another approach would be caching of prepared statements, but i found no
> implementations of this on the net.
>
> We now still need to find a way to hook the stored procs in our O-R mapper:
> generating them the first time a query is done (fairly easy), and
> making "select procname(param1,param2)" behave like a normal query.
>
> We tried a stored procedure returning a cursor and this seemed to work, but
> we'd like to avoid this as, to use cursors, we need to change the core logic
> of our system that decides whether to use transactions and cursors (now the
> system does not create a cursor if it does not expect too many rows coming
> back and so on, and no transaction if no "update" or "insert" queries have to
> be done).