From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | bernhard(dot)lorenz(at)iconsult(dot)at (Bernhard Lorenz) |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] indices: ~* / text_ops |
Date: | 1998-10-02 16:37:23 |
Message-ID: | 199810021637.MAA10824@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> 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?
Indexes on strings can only be used if the start of the string is
anchored with ^ because the index only sorts the strings starting with
the first character.
I have added this to the FAQ, which is on the web site.
>
> 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!
>
Not sure how we handle this. I don't think we handle this, but we
should, but it is hard.
--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-10-02 16:40:56 | Re: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |
Previous Message | Taral | 1998-10-02 16:37:15 | RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF) |