Hello everyone!

What are the techniques for searching large tables without index
(SELECT id FROM big_table WHERE name LIKE '%search%'). With index,
queries are usually fast enough, but queries as above are to slow.

Regards, Pet

Pet wrote:
This is not possible; the only thing I can think of is a full text
search, but this is not what you have in mind:
You could find "I wonder who searches for entries like this", but not
"No research will ever locate this entry".

http://www.postgresql.org/docs/curre...extsearch.html

Yours,
Laurenz Albe

On 5 Aug., 10:00, "Laurenz Albe" <inv... (AT) spam (DOT) to.invalid> wrote:
Hi Laurenz!
Thanks for your reply! I think full text search is in many cases
sufficient and if nothing found, I could fire slowest query then.

What about matching for chars with accent?
If someone search for Andre Rene how can I match to André René? Or
L'Ambre to Ambre or vice versa?
Will full text do that?

Pet wrote:
Hmmm - doesn't that mean that if there is no match, you will
always run a query that does a full table scan?
Are you sure that you would like that?

Not always.

I don't know very much about PostgreSQL's full text search, but
my research has shown that it depends on the dictionary in use.

For "normal" words, PostgreSQL uses a "Snowball dictionary"
by default, which performs "stemming" (linguistic normalization).

The German Snowball stemmer, for example, will convert an
Umlaut like "ü" to the base character "u", while no such
conversion is performed on "é" by the French Snowball stemmer.

You can create your own dictionaries, but I don't know if
you are willing to get that deeply involved
(http://snowball.tartarus.org/).

Yours,
Laurenz Albe

On 5 Aug., 18:37, "Laurenz Albe" <inv... (AT) spam (DOT) to.invalid> wrote:
Ok, it is not often the case.

That's why I'm asking here.

I see. Currently I do conversion to ASCII with python, works fine so
far for any chars

Pet

Pet wrote:
[Full text search]

Sure, if you write a PL/Python function that does the conversion,
you can simply use that in a trigger that populates the "tsvector"
column which you index for full text search.

Then, if you process the query string in a similar way,
it should work fine.

Yours,
Laurenz Albe

Pet wrote:
For the record: Teodor Sigaev has committed an "unaccent" contrib
for version 8.5 that will provide a dictionary and a function that
removes accents.

Yours,
Laurenz Albe