Why are these queries so different in time?
| От | Olivier Hubaut |
|---|---|
| Тема | Why are these queries so different in time? |
| Дата | |
| Msg-id | op.suofx0h694ope3@olivier.amaze.ulb.ac.be обсуждение исходный текст |
| Ответы |
Re: Why are these queries so different in time?
|
| Список | 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 _product7ON (_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 _product7ON (_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 _educt2ON (_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
В списке pgsql-sql по дате отправления: