Re: PostgreSQL runs a query much slower than BDE and MySQL

[Arjen van der Meijden <acmmailing(at)tweakers(dot)net>]

On 16-8-2006 18:48, Peter Hardman wrote:
> Using identically structured tables and the same primary key, if I run this on 
> Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about 3ms, 
> and on PostgresSQL (8.1.3, local server) about 1290ms). All on the same 
> Windows XP Pro machine with 512MB ram of which nearly half is free.  

Is that with or without query caching? I.e. can you test it with SELECT 
SQL_NO_CACHE ... ?
In a read-only environment it will still beat PostgreSQL, but as soon as 
you'd get a read-write environment, MySQL's query cache is of less use. 
So you should compare both the cached and non-cached version, if applicable.

Besides that, most advices on this list are impossible without the 
result of 'explain analyze', so you should probably get that as well.

I'm not sure whether this is the same query, but you might want to try:
SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
FROM SHEEP_FLOCK f1
WHERE
f1.flock_no = '1359'
AND f1.transfer_date = (SELECT MAX(f.transfer_date) FROM SHEEP_FLOCK f 
WHERE regn_no = f1.regn_no)

And you might need an index on (regn_no, transfer_date) and/or one 
combined with that flock_no.

Best regards,

Arjen