Skip site navigation (1) Skip section navigation (2)

Re: Full Text Index Scanning

From: Matt Warner <matt(at)warnertechnology(dot)com>
To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Full Text Index Scanning
Date: 2011-01-30 16:35:47
Message-ID: AANLkTinuPV9wN+JVoQgQGtBJ7NyNCudHkyf9X0hvimgx@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Doesn't seem to work either. Maybe something changed in 9.1?

create index test_idx on testtable using gin(to_tsvector(wordcolumn||'
'||reverse(wordcolumn)));
ERROR:  functions in index expression must be marked IMMUTABLE


On Sun, Jan 30, 2011 at 3:28 AM, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:

> I used 9.1dev, but you can try immutable function (from
> http://andreas.scherbaum.la/blog/archives/10-Reverse-a-text-in-PostgreSQL.html
> )
>
> create function reverse(text) returns text as $$
>
> select case when length($1)>0
>
> then substring($1, length($1), 1) || reverse(substring($1, 1,
> length($1)-1))
>
> else '' end $$ language sql immutable strict;
>
>
>
> On Sat, 29 Jan 2011, Matt Warner wrote:
>
>  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
>>>
>>>
>>
>        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
>

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2011-01-30 17:12:40
Subject: Re: Full Text Index Scanning
Previous:From: Herouth MaozDate: 2011-01-30 16:14:25
Subject: Re: Adding more space, and a vacuum question.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group