Seq scan with a like operator

[Peter Eisentraut]

I don't understand why with a like operator, PostgreSQL 7.4.2 does not

LIKE requires a different kind of index. See
<http://www.postgresql.org/docs/7.4/static/indexes-opclass.html>.

[Jouneau Luc]

Post by Peter Eisentraut
LIKE requires a different kind of index. See
<http://www.postgresql.org/docs/7.4/static/indexes-opclass.html>.

Thanks for the answer peter,

I didn't notice it when I red the doc, but if I create the index as
specified then it is the query with equal operator which use a seq scan.
Do I have to create 2 indexes on the same column (with different datatype)
in order to support different kind of queries ?
Well, It seems quite strange to me :
Suppose you have an user interface in which user can parameter his query on
4 varchar fields (independantly, i.e field 4 does not need to have field 1,2
or 3 filled), and you allow to use generic character such as '*' or '?'
(which will be translated into '%' and '_'). User can also fill in exact
values.
Then you would have to create 4*2=8 indexes to handle every combinations of
possible queries.

It would also mean that support both exact generic queries double the
indexing task on update/insert/delete.

Am I wrong ?

Luc Jouneau


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

Post by Jouneau Luc
I didn't notice it when I red the doc, but if I create the index as
specified then it is the query with equal operator which use a seq scan.
Do I have to create 2 indexes on the same column (with different datatype)
in order to support different kind of queries ?

Yes.

Post by Jouneau Luc
Suppose you have an user interface in which user can parameter his query on
4 varchar fields (independantly, i.e field 4 does not need to have field
1,2 or 3 filled), and you allow to use generic character such as '*' or '?'
(which will be translated into '%' and '_'). User can also fill in exact
values.

I think that kind of interface would use the LIKE operator no matter whether
the user entered wildcards or not.

Post by Jouneau Luc
It would also mean that support both exact generic queries double the
indexing task on update/insert/delete.

Well, if you want to optimize lots of different queries, the system needs to
provide lots of different support.