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

Re: Full Text Index Scanning

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Matt Warner <matt(at)warnertechnology(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Full Text Index Scanning
Date: 2011-01-28 21:12:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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)));
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.


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

Oleg Bartunov, Research Scientist, Head of AstroNet (,
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(495)939-16-83, +007(495)939-23-83

In response to


pgsql-general by date

Next:From: Herouth MaozDate: 2011-01-28 21:12:31
Subject: Adding more space, and a vacuum question.
Previous:From: Jasen BettsDate: 2011-01-28 21:00:21
Subject: Re: error while trying to change the database encoding on a database

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