Re: Benchmarck PostgreSQL 8.1.4 MySQL 5.0.20 and Oracle

On 18.05.2006, at 12:42 Uhr, Olivier Andreotti wrote:

> I use prepared statements for all requests. Each transaction is about
> 5-45 requests.

This may lead to bad plans (at least with 8.0.3 this was the
case) ... I had the same problem a couple of months ago and I
switched from prepared statements with bound values to statements
with "inlined" values:

SELECT
t0.aktiv, t0.id, t0.ist_teilnehmer, t0.nachname, t0.plz, t0.vorname
FROM
public.dga_dienstleister t0
WHERE t0.plz like ?::varchar(256) ESCAPE '|'

withBindings: 1:"53111"(plz)

has changed in my app to:

SELECT
t0.aktiv, t0.id, t0.ist_teilnehmer, t0.nachname, t0.plz, t0.vorname
FROM
public.dga_dienstleister t0
WHERE t0.plz like '53111' ESCAPE '|'

The problem was, that the planner wasn't able to use an index with
the first version because it just didn't know enough about the actual
query.

It might be, that you run into similar problems. An easy way to test
this may be to set the protocolVersion in the JDBC driver connection
url to "2":

jdbc:postgresql://127.0.0.1/Database?protocolVersion=2

cug

--
PharmaLine, Essen, GERMANY
Software and Database Development