Subject: Re: Performance problems with prepared statements
From: Theo Kramer <theo@flame.co.za>
Reply-To: theo@flame.co.za
To: pgsql-performance@postgresql.org
In-Reply-To: <b42b73150710111028h51a6798agf862e5e88cbc123c@mail.gmail.com>
References: <1192027540.2495.3.camel@localhost.localdomain>
	<470CE918.4060608@dalibo.com> <1192044840.2549.21.camel@fedmac>
	<470DE90E.2060605@archonet.com>
	<1192098798.2507.26.camel@localhost.localdomain>
	<b42b73150710111028h51a6798agf862e5e88cbc123c@mail.gmail.com>
Content-Type: text/plain
Organization: Flame Computing Enterprises cc
Date: Fri, 12 Oct 2007 09:38:01 +0200
Message-Id: <1192174681.2470.12.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2007-10-11 at 13:28 -0400, Merlin Moncure wrote:
> On 10/11/07, Theo Kramer <theo@flame.co.za> wrote:
> > On Thu, 2007-10-11 at 10:12 +0100, Richard Huxton wrote:
> > > Theo Kramer wrote:
> > > >
> > > > So I suspect that there is something more fundamental here...
> > >
> > > OK, so there must be something different between the two scenarios. It
> > > can only be one of:
> > >    1. Query
> > > 2. DB Environment (user, locale, settings)
> > >    3. Network environment (server/client/network activity etc)
> >
> > I suspect that it could also be in the way the libpq PQprepare(), and
> > PQexecPrepared() are handled... as opposed to the way PREPARE and
> > EXECUTE are handled.
> 
> PQexecPrepared is generally the fastest way to run queries from a C
> app as long as you get the right plan.   Some suggestions
> 
> * you can explain/explain analyze executing prepared statements from
> psql shell...try that and see if you can reproduce results

Did that - see previous emails in this thread.

> * at worst case you can drop to execParams which is faster (and
> better) than PQexec, at least

Thanks - will keep that option open.

> * if problem is plan related, you can always disable certain plan
> types (seqscan), prepare, and re-enable those plan types
> * do as Jonah suggested, first step is to try and reproduce problem from psql

No success on that.
-- 
Regards
Theo