Date: Fri, 29 Jul 2005 15:33:26 +0200
To: pgsql-sql@postgresql.org
Subject: Why are these queries so different in time?
From: "Olivier Hubaut" <olivier@scmbb.ulb.ac.be>
Organization: SCMBB
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.suofx0h694ope3@olivier.amaze.ulb.ac.be>
User-Agent: Opera M2/8.01 (MacPPC, build 2092)

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