Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group