Re: Performance problems with prepared statements

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