[SQL] Why are these queries so different in time?

[Olivier Hubaut]

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match