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: <470DE90E.2060605@archonet.com>
References: <1192027540.2495.3.camel@localhost.localdomain>
	<470CE918.4060608@dalibo.com> <1192044840.2549.21.camel@fedmac>
	<470DE90E.2060605@archonet.com>
Content-Type: text/plain
Organization: Flame Computing Enterprises cc
Date: Thu, 11 Oct 2007 12:33:18 +0200
Message-Id: <1192098798.2507.26.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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.

> 
> Are you sure you have the parameter types correct in your long-running 
> query?

Yes - the problem surfaced during a going live session on an 80 user
system... and we had to roll back to the previous system in a hurry.
This was a part of the application that had missed testing, but I have
had other reports from some of my other systems where this appears to be
a problem but not of the magnitude that this one is.

In any case I have managed to reproduce it in my test environment with
configuration settings the same.

> Try setting log_min_duration_statement=9000 or so to capture 
> long-running queries.

Thanks - will give that a try.

> 
> Make sure the user and any custom settings are the same. Compare SHOW 
> ALL for both ways.

> You've said elsewhere you've ruled out the network environment, so 
> there's not point worrying about that further.

It is definitely not a network problem - ie. the postgresql server load
goes way up when this query is run.

-- 
Regards
Theo