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

Regards
=====================================
SELECT
E1A1.nameInstance AS inste,
E1A1.nameClass AS classe,
E1A1.value AS dx,
E1A2.value AS dy,
E1A3.value AS dz,
E1A4.value AS v,
I0.value AS ix,
I1.value AS iy,
I2.value AS iz,
I3.value AS iv
FROM
instance_Attribute AS E1A1,
instance_Attribute AS E1A2,
instance_Attribute AS E1A3,
instance_Attribute AS E1A4,
Influence AS I0,
Influence AS I1,
Influence AS I2,
Influence AS I3
WHERE
E1A1.nameAttribute LIKE 'directionx' AND
E1A2.nameInstance LIKE E1A1.nameInstance  AND
E1A2.nameClass LIKE E1A1.nameClass  AND
E1A2.nameAttribute LIKE 'directiony' AND
E1A3.nameInstance LIKE E1A1.nameInstance  AND
E1A3.nameClass LIKE E1A1.nameClass  AND
E1A3.nameAttribute LIKE 'directionz' AND
E1A4.nameInstance LIKE E1A1.nameInstance  AND
E1A4.nameClass LIKE E1A1.nameClass  AND
E1A4.nameAttribute LIKE 'vitesse' AND
I0.nameClass LIKE E1A1.nameClass AND
I0.nameInstance LIKE E1A1.nameInstance AND
I0.nameInfluence LIKE 'inf_directionx' AND
I1.nameClass LIKE E1A1.nameClass AND
I1.nameInstance LIKE E1A1.nameInstance AND
I1.nameInfluence LIKE 'inf_directiony' AND
I2.nameClass LIKE E1A1.nameClass AND
I2.nameInstance LIKE E1A1.nameInstance AND
I2.nameInfluence LIKE 'inf_directionz' AND
I3.nameClass LIKE E1A1.nameClass AND
I3.nameInstance LIKE E1A1.nameInstance AND
I3.nameInfluence LIKE 'inf_vitesse' ;

Michel Soto
----------------------------------------------------------------------------
Universite Pierre et Marie Curie     TEL: +33 1 44 27 88 30
Laboratoire LIP6-CNRS                          +33 1 44 55 35 23
8, rue du Capitaine Scott                FAX: +33 1 44 27 53 53
75015 PARIS                                  mailto:Michel.Soto@lip6.fr
France

Accï¿½s: http://www.mappy.fr/PlanPerso/7438/1

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
Regards
=====================================
SELECT 
E1A1.nameInstance AS inste, 
E1A1.nameClass AS classe, 
E1A1.value AS dx, 
E1A2.value AS dy, 
E1A3.value AS dz, 
E1A4.value AS v, 
I0.value AS ix, 
I1.value AS iy, 
I2.value AS iz, 
I3.value AS iv 
FROM 
instance_Attribute AS E1A1, 
instance_Attribute AS E1A2, 
instance_Attribute AS E1A3, 
instance_Attribute AS E1A4, 
Influence AS I0, 
Influence AS I1, 
Influence AS I2, 
Influence AS I3 
WHERE
E1A1.nameAttribute LIKE 'directionx' AND 
E1A2.nameInstance LIKE E1A1.nameInstance  AND 
E1A2.nameClass LIKE E1A1.nameClass  AND 
E1A2.nameAttribute LIKE 'directiony' AND 
E1A3.nameInstance LIKE E1A1.nameInstance  AND 
E1A3.nameClass LIKE E1A1.nameClass  AND 
E1A3.nameAttribute LIKE 'directionz' AND 
E1A4.nameInstance LIKE E1A1.nameInstance  AND 
E1A4.nameClass LIKE E1A1.nameClass  AND 
E1A4.nameAttribute LIKE 'vitesse' AND 
I0.nameClass LIKE E1A1.nameClass AND 
I0.nameInstance LIKE E1A1.nameInstance AND 
I0.nameInfluence LIKE 'inf_directionx' AND 
I1.nameClass LIKE E1A1.nameClass AND 
I1.nameInstance LIKE E1A1.nameInstance AND 
I1.nameInfluence LIKE 'inf_directiony' AND 
I2.nameClass LIKE E1A1.nameClass AND 
I2.nameInstance LIKE E1A1.nameInstance AND 
I2.nameInfluence LIKE 'inf_directionz' AND 
I3.nameClass LIKE E1A1.nameClass AND 
I3.nameInstance LIKE E1A1.nameInstance AND 
I3.nameInfluence LIKE 'inf_vitesse' ;

Michel Soto
----------------------------------------------------------------------------
Universite Pierre et Marie Curie     TEL: +33 1 44 27
88 30
Laboratoire
LIP6-CNRS                         
+33 1 44 55 35 23
8, rue du Capitaine
Scott               
FAX: +33 1 44 27 53 53 
75015
PARIS                                 
mailto:Michel.Soto@lip6.fr

France
Accï¿½s: http://www.mappy.fr/PlanPerso/7438/1
Wed, 04 Jul 2001 16:30:18 +0200
Michel Soto <Michel.Soto@lip6.fr>
Strange query execution time