Generic search

From: "jerome doucerain" <jerome(dot)doucerain(at)chu-nimes(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Generic search
Date: 1998-12-04 11:17:37
Message-ID: 199812041030.LAA05526@mail.chu-nimes.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
As it seems
Postgres doesn't use index when the where clause contains "like" operator.
It only uses it in case of "=" operator.
for example :
test=> create table essai (nom varchar(50));
CREATE
test=> create index essai_nom on essai (nom);
CREATE
test=> \d essai

Table = essai
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| nom | varchar() |
50 |
+----------------------------------+----------------------------------+-----
--+
test=> \d essai_nom

Table = essai_nom
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| nom | varchar() |
-4 |
+----------------------------------+----------------------------------+-----
--+
test=> explain select * from essai where nom='abc';
NOTICE: QUERY PLAN:

Index Scan using essai_nom on essai (cost=0.00 size=0 width=12)

EXPLAIN
test=> explain select * from essai where nom like 'a%';
NOTICE: QUERY PLAN:

Seq Scan on essai (cost=0.00 size=0 width=12)

EXPLAIN

Postgres 6.4 Linux 2.0.34.

So I'm looking for a way to get quite good performances with generic
search.
Thank you for advices.
Jerome.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alex Oboimov 1998-12-04 11:46:16 createuser -h
Previous Message Nathan Dietsch 1998-12-04 11:01:07 [GENERAL] compiling postgresql 6.4 in BSDi