Thread: Strange query execution time

Strange query execution time

From
Michel Soto
Date:
Hi,<br /><br /> Can  someone explain why the following query takes  1 second when using LIKE and takes 30 seconds when
replacingLIKE 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.nameInstance AS inste, <br
/>E1A1.nameClass AS classe, <br /> E1A1.value AS dx, <br /> E1A2.value AS dy, <br /> E1A3.value AS dz, <br />
E1A4.valueAS v, <br /> I0.value AS ix, <br /> I1.value AS iy, <br /> I2.value AS iz, <br /> I3.value AS iv <br /> FROM
<br/> instance_Attribute AS E1A1, <br /> instance_Attribute AS E1A2, <br /> instance_Attribute AS E1A3, <br />
instance_AttributeAS E1A4, <br /> Influence AS I0, <br /> Influence AS I1, <br /> Influence AS I2, <br /> Influence AS
I3<br /> WHERE<br /> E1A1.nameAttribute LIKE 'directionx' AND <br /> E1A2.nameInstance LIKE 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.nameClass LIKE E1A1.nameClass  AND <br />
E1A4.nameAttributeLIKE 'vitesse' AND <br /> I0.nameClass LIKE E1A1.nameClass AND <br /> I0.nameInstance LIKE
E1A1.nameInstanceAND <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.nameClassAND <br /> I2.nameInstance LIKE E1A1.nameInstance AND <br /> I2.nameInfluence LIKE 'inf_directionz' AND
<br/> I3.nameClass LIKE E1A1.nameClass AND <br /> I3.nameInstance LIKE E1A1.nameInstance AND <br /> I3.nameInfluence
LIKE'inf_vitesse' ;<br /><p><font size="3">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>

Re: Strange query execution time

From
Stephan Szabo
Date:
What does explain show for the two queries?

On Wed, 4 Jul 2001, Michel Soto wrote:

> Hi,
> 
> Can  someone explain why the following query takes  1 second when using 
> LIKE and takes 30 seconds when replacing LIKE by = in the WHERE ?
> - instance_Attribute  has 45 rows and Influence has 5 rows.
> - Postgresql 7.1