Re: Full Text Index Scanning

9.0.2

On Sat, Jan 29, 2011 at 9:35 AM, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:

> 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(at)warnertechnology(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>