Re: best practise/pattern for large OR / LIKE searches

The wildspeed function seems to be what I was looking for.

an dI remember that I have seen it before on the list... just I
couldn't remember names or anything...

Ries

On Aug 26, 2009, at 7:28 AM, Pavel Stehule wrote:

> 2009/8/26 <tv(at)fuzzy(dot)cz>:
>> Hi Pavel,
>>
>> can you provide some link or other directions to the proposal? I
>> guess it
>> was posted to this list or somewhere else?
>
> Please, ask to Oleg Bartunov
>
> http://www.sai.msu.su/~megera/wiki/wildspeed
>
> regards
> Pavel Stehule
>
>>
>> Tomas
>>
>>> Hello
>>>
>>> one year ago there was proposal for index support for LIKE %some%.
>>> The
>>> problem was extreme size of index size.
>>>
>>> I thing so you can write own C function, that can check string
>>> faster
>>> than repeated LIKE
>>>
>>> some like
>>>
>>> SELECT * FROM tbl WHERE contains(datanumber, '12345','54321',....)
>>>
>>> regards
>>> Pavel Stehule
>>>
>>> 2009/8/26 Ries van Twisk <pg(at)rvt(dot)dds(dot)nl>:
>>>> Hey All,
>>>> I am wondering if there is a common pattern for these sort of
>>>> queries :
>>>> SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE
>>>> '%54321%' OR
>>>> LIKE
>>>> '%8766%' OR LIKE '%009%', ..
>>>> The number of OR/LIKES are in the order of 50-100 items...
>>>> the table tbl is a couple of million rows.
>>>> The datanumber is a string that are maximum 10 characters long, no
>>>> spaces
>>>> and can contain numbers and letters.
>>>> Apart from creating a couple of index table to make the LIKE left
>>>> anchored
>>>> something like this :
>>>> tbl <----> tbl_4letters
>>>> tbl <----> tbl_5letters
>>>> tbl <----> tbl_3letters
>>>> or creating a functional index 'of some sort' are there any other
>>>> brilliant
>>>> ideas out there to solve such a problem (GIN/GIS???) ?
>>>> Searches are currently taking to long and we would like to optimize
>>>> them,
>>>> but before we dive into our own solution we
>>>> where wondering if there already common solutions for this...
>>>> Kind Regards,
>>>> Ries van Twisk
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>>

regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries(at)vantwisk(dot)nl web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196 Cell: +593 9901 7694 SIP:
+1-747-690-5133