From: | Bernhard Lorenz <bernhard(dot)lorenz(at)iconsult(dot)at> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | indices: ~* / text_ops |
Date: | 1998-10-02 16:01:19 |
Message-ID: | 199810021601.SAA27700@orade.iconsult.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
hullo,
ive two serious problems with postgresql (latest version):
1.) i seem to be unable (well, i _am_ ;-) to create an index
on a text field and then have that index being used if
i perform a search using ~*, ~~, and related operators.
i found out that these operators can only successfully
be implemented if i use box* field types etc. this is a real
pain, since i have that database with more than 70,000
entries and it always does a sequential scan.
a query might look like
"select * from table where field ~* 'string'" or
"select * from table where field ~~ '%string%' etc.
there are indices (hash, btree, (field text_ops)), but
they wont be used.
can anybody of you possibly help me any further on this issue?
2.) another thing i noticed is that while "~*" is supposed to
perform a case insensitive search, it does not. i havent
checked too much into the bug behind it (like "all ascii
values > 127 ..."), but the typical austrian and german
characters like "ae" with two dots (forgot the terminologically
exact name, forgive me) etc. will not be searched
properly, thus, if i have a field entry with
'AEyadayada'
and perform a search with
... ~* 'aeyadayada',
it wont find anything, i have to use ~* 'AEyadayada'.
opinions on that one woul dbe appreciated as well!
best regards,
+bl.
--
Bernhard Lorenz Managing Partner
!C Internet Consult http://www.iconsult.at/
Pacassistrasse 32, A-1130 Wien +43/1/319 09 90
Aichholzgasse 6/5, A-1120 Wien +43/1/817 39 23
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-02 16:05:14 | Re: [HACKERS] SQL92 |
Previous Message | Jan Wieck | 1998-10-02 15:45:06 | Re: [HACKERS] Open 6.4 items |