From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL runs a query much slower than BDE and MySQL
Date: Wed, 16 Aug 2006 12:02:31 -0600
Message-ID: <a55915760608161102j6f93402aufaafdaee0ef49278@mail.gmail.com> (raw)
In-Reply-To: <44E35A5D.19933.35F376@peter.ssbg.zetnet.co.uk>
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
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: rdeleonp@gmail.com
Subject: Re: PostgreSQL runs a query much slower than BDE and MySQL
In-Reply-To: <a55915760608161102j6f93402aufaafdaee0ef49278@mail.gmail.com>
* 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