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.
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 |