pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy are these queries so different in time?
2+ messages / 2 participants
[nested] [flat]
* Why are these queries so different in time?
@ 2005-07-29 13:33 Olivier Hubaut <olivier@scmbb.ulb.ac.be>
2005-07-29 15:42 ` Re: Why are these queries so different in time? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Olivier Hubaut @ 2005-07-29 13:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Why are these queries so different in time?
2005-07-29 13:33 Why are these queries so different in time? Olivier Hubaut <olivier@scmbb.ulb.ac.be>
@ 2005-07-29 15:42 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-07-29 15:42 UTC (permalink / raw)
To: Olivier Hubaut <olivier@scmbb.ulb.ac.be>; +Cc: pgsql-sql
Olivier Hubaut wrote:
> 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
(4 LEFT JOINS then a couple of WHERE conditions on 2 tables)
> 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
(Two lots of 2 x Left-joins, unioned together)
> 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?
You're probably processing 1000 more rows in the first example. It's
probably running the LEFT JOIN across all the tables then restricting
the results in the WHERE. As it happens you're throwing away duplicates
with DISTINCT and/or UNION anyway, so you never get to see the results.
Try an EXPLAIN ANALYSE of the first example and see if the rows= parts
indicate very large numbers of rows being processed.
To make it faster I'd remove the LEFT JOINs, since your WHERE conditions
seem to rule out the NULL cases anyway.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-07-29 15:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-29 13:33 Why are these queries so different in time? Olivier Hubaut <olivier@scmbb.ulb.ac.be>
2005-07-29 15:42 ` Richard Huxton <dev@archonet.com>
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