From: Olivier Hubaut <olivier@scmbb.ulb.ac.be>
To: pgsql-sql@postgresql.org
Subject: Why are these queries so different in time?
Date: Fri, 29 Jul 2005 15:33:26 +0200
Message-ID: <op.suofx0h694ope3@olivier.amaze.ulb.ac.be> (raw)
Hi, I have a question about performance querying a 7.4 database. The
orginal generated query was
SELECT DISTINCT _compound0.object_id AS "ObjectId"
FROM
amaze._compound _compound0
LEFT JOIN amaze._product _product7 ON (_compound0.object_id =
_product7.compound)
LEFT JOIN amaze._database_object _database_object11 ON
(_product7.reaction = _database_object11.object_id)
LEFT JOIN amaze._educt _educt2 ON (_compound0.object_id =
_educt2.compound)
LEFT JOIN amaze._database_object _database_object6 ON
(_educt2.reaction = _database_object6.object_id)
WHERE
(
_database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
OR
_database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
);
This on take a huge time to perform, which may come to a timeout on the
front-end application that uses the database.
So, I decided to modify manually the query like this:
SELECT DISTINCT _compound0.object_id AS "ObjectId"
FROM
amaze._compound _compound0
LEFT JOIN amaze._product _product7 ON (_compound0.object_id =
_product7.compound)
LEFT JOIN amaze._database_object _database_object11 ON
(_product7.reaction = _database_object11.object_id)
WHERE
(
_database_object11.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
)
UNION
SELECT DISTINCT _compound0.object_id AS "ObjectId"
FROM
amaze._compound _compound0
LEFT JOIN amaze._educt _educt2 ON (_compound0.object_id =
_educt2.compound)
LEFT JOIN amaze._database_object _database_object6 ON
(_educt2.reaction = _database_object6.object_id)
WHERE
(
_database_object6.label = '2-Isopropylmalate + CoA <= Acetyl-CoA
+ 3-Methyl-2-oxobutanoate + H2O'
)
This should give the same result set, but it's really faster than the
previous one, more than one thousand time faster.
Is there a reason for this huge difference of performance?
Thanks in advance.
--
Olivier Hubaut
North Bears Team
SCMBB - ULB
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-sql@postgresql.org
Cc: olivier@scmbb.ulb.ac.be
Subject: Re: Why are these queries so different in time?
In-Reply-To: <op.suofx0h694ope3@olivier.amaze.ulb.ac.be>
* 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