DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=YhS1eSKPlSloAdIty+Fz4c3a2GzxMU1O6xRJQHJ2RsBxsPL+TeC5zP417VlT5w5pFxGRe/TZjafQKpF2SceuQtDnBzArRUMjnZoYS1AphlW2yI/HGAFzVa7F5dJAwuXfuEPSHMCz7tvIgxWXeGyTjwIRQcBXYuF8a0xRGdUJk2E=
Message-ID: <a55915760608161102j6f93402aufaafdaee0ef49278@mail.gmail.com>
Date: Wed, 16 Aug 2006 12:02:31 -0600
From: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL runs a query much slower than BDE and MySQL
In-Reply-To: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>

On 8/16/06, Peter Hardman <peter@ssbg.zetnet.co.uk> wrote:
> I'm in the process of migrating a Paradox 7/BDE 5.01 database from single-user
> Paradox to a web based interface to either MySQL or PostgreSQL.
> The database is a pedigree sheep breed society database recording sheep and
> flocks (amongst other things).
>
> My current problem is with one table and an associated query which takes 10
> times longer to execute on PostgreSQL than BDE, which in turn takes 10 times
> longer than MySQL. The table links sheep to flocks and is created as follows:
>
> CREATE TABLE SHEEP_FLOCK
> (
>   regn_no varchar(7) NOT NULL,
>   flock_no varchar(6) NOT NULL,
>   transfer_date date NOT NULL,
>   last_changed date NOT NULL,
>   CONSTRAINT SHEEP_FLOCK_pkey PRIMARY KEY (regn_no, flock_no,
> transfer_date)
> )
> WITHOUT OIDS;
> ALTER TABLE SHEEP_FLOCK OWNER TO postgres;
>
> I then populate the table with
>
> COPY SHEEP_FLOCK
> FROM 'e:/ssbg/devt/devt/export_data/sheep_flock.txt'
> WITH CSV HEADER
>
> The table then has about 82000 records
>
> The query I run is:
>
> /* Select all sheep who's most recent transfer was into the subject flock */
> SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1 JOIN
>     /* The last transfer date for each sheep */
>     (SELECT f.regn_no, MAX(f.transfer_date) as last_xfer_date
>     FROM  SHEEP_FLOCK f
>     GROUP BY f.regn_no) f2
> ON f1.regn_no = f2.regn_no
> WHERE f1.flock_no = '1359'
> AND f1.transfer_date = f2.last_xfer_date
>
> The sub-select on it's own returns about 32000 rows.
>
> 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.
>
> The query plan shows most of the time is spent sorting the 30000+ rows from the subquery, so I added a further
> subquery as follows:
>
> /* Select all sheep who's most recent transfer was into the subject flock */
> SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1 JOIN
>     /* The last transfer date for each sheep */
>     (SELECT f.regn_no, MAX(f.transfer_date) as last_xfer_date
>     FROM  SHEEP_FLOCK f
>     WHERE f.regn_no IN
>         /* Limit the rows extracted by the outer sub-query to those relevant to the
> subject flock */
>         /* This typically reduces the time from 1297ms to 47ms - from 35000 rows
> to 127 rows */
>         (SELECT s.regn_no FROM SHEEP_FLOCK s where s.flock_no = '1359')
>     GROUP BY f.regn_no) f2
> ON f1.regn_no = f2.regn_no
> WHERE f1.flock_no = '1359'
> AND f1.transfer_date = f2.last_xfer_date
>
> then as the comment suggests I get a considerable improvement, but it's still an
> order of magnitude slower than MySQL.
>
> Can anyone suggest why PostgreSQL performs the original query so much slower than even BDE?

ANALYZE?

Regards,

Rodrigo