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