Thread: indexing large "text" attributes ... ERROR: maximum size is 8191

I get the following error when tying to index a large text field:

dev=# CREATE INDEX idx_fulltext_articel_block ON fulltext_article
(article_block);
ERROR:  index row requires 8724 bytes, maximum size is 8191

I am actually creating a GIN index on another field but I need to
index the original "text" field to perform exact phrase matches.
Does anybody know how to fix this or alternatively perform exact
phrase matches on a GIN index?

Thanks in advance!

Benjamin

Benjamin Arai <me@benjaminarai.com> writes:
> I am actually creating a GIN index on another field but I need to
> index the original "text" field to perform exact phrase matches.

Why do you think an extra index will be useful for that?  Especially
a btree index?

            regards, tom lane

To clarify, I am using the GIN index to perform the tsearch2 queries
which works fine.  The problem with this is that you cannot use the
tsearch2 fields since they are vectors to do exact match searches.
The tsearch2 webpage says to do something like

         SELECT intindex, strTopic FROM tblmessages
                 WHERE idxfti @@ to_tsquery('default', 'gettysburg &
address')
                 AND strMessage ~* '.*men are created equal.*';
          intindex |           strtopic
         ----------+------------------------------
                 6 | Gettysburg address quotation
         (1 row)

Which would mean I would have to create index on strMessage.  Right?

Benjamin

On Aug 5, 2007, at 7:17 PM, Tom Lane wrote:

> Benjamin Arai <me@benjaminarai.com> writes:
>> I am actually creating a GIN index on another field but I need to
>> index the original "text" field to perform exact phrase matches.
>
> Why do you think an extra index will be useful for that?  Especially
> a btree index?
>
>             regards, tom lane
>

Benjamin Arai <me@benjaminarai.com> writes:
> The tsearch2 webpage says to do something like

>          SELECT intindex, strTopic FROM tblmessages
>                  WHERE idxfti @@ to_tsquery('default', 'gettysburg &
> address')
>                  AND strMessage ~* '.*men are created equal.*';
>           intindex |           strtopic
>          ----------+------------------------------
>                  6 | Gettysburg address quotation
>          (1 row)

Right.

> Which would mean I would have to create index on strMessage.  Right?

Wrong.  A btree index on strMessage is utterly worthless for checking
that pattern match, and even if it did work, there's no point in using
a second index to check it rather than fetching the heap entry.

            regards, tom lane