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.