Thread: Strange query execution time

Strange query execution time

From
Michel Soto
Date:
<font size="3">Hi,<br /><br /> Can  someone explain why the following query takes  1 second when using LIKE and takes
30seconds when replacing LIKE by = in the WHERE ?<br /> - instance_Attribute  has 45 rows and Influence has 5 rows.<br
/>- Postgresql 7.1<br /><br /> Regards<br /> =====================================<br /> SELECT <br />
E1A1.nameInstanceAS inste, <br /> E1A1.nameClass AS classe, <br /> E1A1.value AS dx, <br /> E1A2.value AS dy, <br />
E1A3.valueAS dz, <br /> E1A4.value AS v, <br /> I0.value AS ix, <br /> I1.value AS iy, <br /> I2.value AS iz, <br />
I3.valueAS iv <br /> FROM <br /> instance_Attribute AS E1A1, <br /> instance_Attribute AS E1A2, <br />
instance_AttributeAS E1A3, <br /> instance_Attribute AS E1A4, <br /> Influence AS I0, <br /> Influence AS I1, <br />
InfluenceAS I2, <br /> Influence AS I3 <br /> WHERE<br /> E1A1.nameAttribute LIKE 'directionx' AND <br />
E1A2.nameInstanceLIKE E1A1.nameInstance  AND <br /> E1A2.nameClass LIKE E1A1.nameClass  AND <br /> E1A2.nameAttribute
LIKE'directiony' AND <br /> E1A3.nameInstance LIKE E1A1.nameInstance  AND <br /> E1A3.nameClass LIKE E1A1.nameClass 
AND<br /> E1A3.nameAttribute LIKE 'directionz' AND <br /> E1A4.nameInstance LIKE E1A1.nameInstance  AND <br />
E1A4.nameClassLIKE E1A1.nameClass  AND <br /> E1A4.nameAttribute LIKE 'vitesse' AND <br /> I0.nameClass LIKE
E1A1.nameClassAND <br /> I0.nameInstance LIKE E1A1.nameInstance AND <br /> I0.nameInfluence LIKE 'inf_directionx' AND
<br/> I1.nameClass LIKE E1A1.nameClass AND <br /> I1.nameInstance LIKE E1A1.nameInstance AND <br /> I1.nameInfluence
LIKE'inf_directiony' AND <br /> I2.nameClass LIKE E1A1.nameClass AND <br /> I2.nameInstance LIKE E1A1.nameInstance AND
<br/> I2.nameInfluence LIKE 'inf_directionz' AND <br /> I3.nameClass LIKE E1A1.nameClass AND <br /> I3.nameInstance
LIKEE1A1.nameInstance AND <br /> I3.nameInfluence LIKE 'inf_vitesse' ;<br /><p> Michel Soto<br />
----------------------------------------------------------------------------<br/> Universite Pierre et Marie Curie    
TEL:+33 1 44 27 88 30<br /> Laboratoire LIP6-CNRS                          +33 1 44 55 35 23<br /> 8, rue du Capitaine
Scott               FAX: +33 1 44 27 53 53 <br /> 75015 PARIS                                  <a eudora="autourl"
href="mailto:Michel.Soto@lip6.fr">mailto:Michel.Soto@lip6.fr</a><br/> France<br /><br /> Accès: <a eudora="autourl"
href="http://www.mappy.fr/PlanPerso/7438/1">http://www.mappy.fr/PlanPerso/7438/1</a></font>