Strange query execution time

From: Michel Soto <Michel(dot)Soto(at)lip6(dot)fr>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Strange query execution time
Date: 2001-07-04 12:12:51
Message-ID: 5.1.0.14.0.20010704133435.00a2a520@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(dot)Soto(at)lip6(dot)fr
France

Accès: http://www.mappy.fr/PlanPerso/7438/1

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Damien Clermonté 2001-07-04 12:51:49 Re: [PATCH] Patch to make pg_hba.conf handle virtualhost access control and samehost keyword
Previous Message Colin Strickland 2001-07-04 12:00:27 Re: [OT] Any major users of postgresql?