Strange query execution time
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
Can someone explain why the following query takes 1 second when using
LIKE and takes 30 seconds when replacing LIKE by = in the WHERE ?
Because there is no optimization built in, that notices, that your
string does not contain a wildcard and would translate the restriction
correspondingly. It is currently executed more or less like below:
(I3.nameInfluence >= 'inf_vitesse' and I3.nameInfluence < 'inf_vitessf'
and I3.nameInfluence LIKE 'inf_vitesse')
I already wanted to try to add this optimization myself, but lacked the time so far.
Anybody want to volunteer ?
TODO: add LIKE optimization to use = if constant does not contain any wildcards
Andreas
Import Notes
Resolved by subject fallback
