From: Theo Kramer <theo@flame.co.za>
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
Date: Fri, 12 Oct 2007 16:57:01 +0200
Message-ID: <1192201021.6170.47.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: theo@flame.co.za
Subject: Re: Performance problems with prepared statements
In-Reply-To: <1192201021.6170.47.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox