Re: Full Text Index Scanning - Mailing list pgsql-general

What version of Pg you run ? Try latest version.

Oleg

On Sat, 29 Jan 2011, Matt Warner wrote:

> Reverse isn't a built-in Postgres function, so I found one and installed it.
> However, attempting to use it in creating an index gets me the message
> "ERROR:  functions in index expression must be marked IMMUTABLE", even
> though the function declaration already has the immutable argument.
>
> Is there a specific version of the reverse function you're using? Or am I
> just missing something obvious? This is Postgres 9, BTW.
>
> Thanks,
>
> Matt
>
> On Sat, Jan 29, 2011 at 6:46 AM, Matt Warner <matt@warnertechnology.com>wrote:
>
>> Thanks Oleg. I'm going to have to experiment with this so that I understand
>> it better.
>>
>> Matt
>>
>>
>> On Fri, Jan 28, 2011 at 1:12 PM, Oleg Bartunov <oleg@sai.msu.su> wrote:
>>
>>> Matt, I'd try to use prefix search on original string concatenated with
>>> reverse string:
>>>
>>> Just tried on some spare table
>>>
>>> knn=# \d spot_toulouse
>>>            Table "public.spot_toulouse"
>>>       Column        |       Type        | Modifiers
>>> ---------------------+-------------------+-----------
>>>  clean_name          | character varying |
>>>
>>>
>>> 1. create index knn=# create index clean_name_tlz_idx on spot_toulouse
>>> using gin(to_tsvector('french', clean_name || ' ' || reverse(clean_name)));
>>> 2.
>>> select clean_name from spot_toulouse where to_tsvector('french',
>>> clean_name|| ' ' ||  reverse(clean_name) ) @@ to_tsquery('french','the:* |
>>> et:*');
>>>
>>> Select looks cumbersome, but you can always write wrapper functions. The
>>> only drawback I see for now is that ranking function will a bit confused,
>>> since coordinates of original and reversed words will be not the same, but
>>> again, it's possible to obtain tsvector by custom function, which aware
>>> about reversing.
>>>
>>> Good luck and let me know if this help you.
>>>
>>> Oleg
>>>
>>>
>>> On Fri, 28 Jan 2011, Matt Warner wrote:
>>>
>>>  I'm in the process of migrating a project from Oracle to Postgres and
>>>> have
>>>> run into a feature question. I know that Postgres has a full-text search
>>>> feature, but it does not allow scanning the index (as opposed to the
>>>> data).
>>>> Specifically, in Oracle you can do "select * from table where
>>>> contains(colname,'%part_of_word%')>1". While this isn't terribly
>>>> efficient,
>>>> it's much faster than full-scanning the raw data and is relatively quick.
>>>>
>>>> It doesn't seem that Postgres works this way. Attempting to do this
>>>> returns
>>>> no rows: "select * from table where to_tsvector(colname) @@
>>>> to_tsquery('%part_of_word%')"
>>>>
>>>> The reason I want to do this is that the partial word search does not
>>>> involve dictionary words (it's scanning names).
>>>>
>>>> Is this something Postgres can do? Or is there a different way to do scan
>>>> the index?
>>>>
>>>> TIA,
>>>>
>>>> Matt
>>>>
>>>>
>>>        Regards,
>>>                Oleg
>>> _____________________________________________________________
>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>>> Sternberg Astronomical Institute, Moscow University, Russia
>>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>>> phone: +007(495)939-16-83, +007(495)939-23-83
>>>
>>
>>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83