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: <b42b73150710120602g2f47dd21lb851a72d05a184f1@mail.gmail.com>
References: <1192027540.2495.3.camel@localhost.localdomain>
	<470CE918.4060608@dalibo.com> <1192044840.2549.21.camel@fedmac>
	<slrnfgsqpi.27gg.andrew+nonews@atlantis.supernews.net>
	<b42b73150710111304m161814cds984a7c525ff87241@mail.gmail.com>
	<1192174502.2470.9.camel@localhost.localdomain>
	<b42b73150710120602g2f47dd21lb851a72d05a184f1@mail.gmail.com>
Content-Type: text/plain
Organization: Flame Computing Enterprises cc
Date: Fri, 12 Oct 2007 16:57:01 +0200
Message-Id: <1192201021.6170.47.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2007-10-12 at 09:02 -0400, Merlin Moncure wrote:
> fwiw, I converted a pretty large cobol app (acucobol) to postgresql
> backend translating queries on the fly.  if this is a fresh effort,
> you definately want to use the row-wise comparison feature of 8.2.
> not only is it much simpler, it's much faster.  with some clever
> caching strategies i was able to get postgresql performance to exceed
> the isam backend.  btw, I used execprepared for virtually the entire
> system.
> 
> example read next:
> select * from foo where (a,b,c) > (a1,b1,c1) order by a,b,c limit 25;
> 
> example read previous:
> select * from foo where (a,b,c) < (a1,b1,c1) order by a desc, b desc,
> c desc limit 25;
> 
> etc.  this will use complete index for a,b,c and is much cleaner to
> prepare, and parse for the planner (the best you can get with standard
> tactics is to get backend to use index on a).
> 
> Another big tip i can give you (also 8.2) is to check into advisory
> locks for isam style pessimistic locking.  With some thin wrappers you
> can generate full row and table locking which is quite powerful.

Very interesting - I have largely done the same thing, creating tables
on the fly, translating isam calls, and creating, preparing and
executing queries on the fly using the libpq PQprepare() and
PQexecPrepared() statements... and it is running rather well at several
sites, however, the initial port I did was for 8.0 and 8.1 so could not,
at the time use, row  level comparison, although I do have it on the
latest version of my code working on 8.2 which is not yet released.

The problem I have on row level comparison is that we have orders that
are mixed, ie. a mixture of ascending and descending orders and do not
know if it is possible to use row level comparison on that... eg. I
haven't been able to transform the following it a row comparison query.

select * from foo where
  (a = a1 and b = b1 and c >= c1) or
  (a = a1 and b < b1) or
  (a > a1)
order by a, b desc, c;

I have, however, found that transforming the above into a union based
query performs substantially better.

Also indexes containing mixed order columns will only be available on
8.3...

But many thanks for your advice.

-- 
Regards
Theo