From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Peter Hardman <peter@ssbg.zetnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL runs a query much slower than BDE and MySQL
Date: Wed, 16 Aug 2006 20:02:24 +0200
Message-ID: <44E35DB0.8050109@tweakers.net> (raw)
In-Reply-To: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>
References: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>
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
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: acmmailing@tweakers.net, peter@ssbg.zetnet.co.uk
Subject: Re: PostgreSQL runs a query much slower than BDE and MySQL
In-Reply-To: <44E35DB0.8050109@tweakers.net>
* 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