Re: Fulltext index - Mailing list pgsql-general

From Tom Lane
Subject Re: Fulltext index
Date
Msg-id 18922.1226167035@sss.pgh.pa.us
Whole thread Raw
In response to Fulltext index  (Andreas Kraftl <andreas.kraftl@kraftl.at>)
List pgsql-general
Andreas Kraftl <andreas.kraftl@kraftl.at> writes:
> CREATE INDEX idx ON table USING gin(
> to_tsvector(
>     case
>         when a = 'de' then 'german'
>         when a = 'en' then 'english'
>         else 'english'
>     end
> ), b);

> This doesn't work. Error Message in german:
> FEHLER: Zugriffsmethode »gin« unterstützt keine mehrspaltigen Indexe
> SQL state: 0A000
> means, gin doesn't accept multicolumn indexes.

You've got the parentheses in the wrong place.

The way I'd suggest doing this is

regression=# create table tab (a regconfig, b text);
CREATE TABLE
regression=# create index idx on tab using gin(to_tsvector(a,b));
CREATE INDEX
regression=# explain select * from tab where to_tsvector(a,b) @@ to_tsquery('english','foo');
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using idx on tab  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: (to_tsvector(a, b) @@ '''foo'''::tsquery)
(2 rows)

If you want to use abbreviations like 'en' and 'de', create text search
configurations named that way instead of inserting a run-time
conversion.

            regards, tom lane

pgsql-general by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: Fulltext index
Next
From: Tom Lane
Date:
Subject: Re: After delete trigger problem