Re: [HACKERS] Index greater than 8k - Mailing list pgsql-general

Joshua D. Drake wrote:
> Alvaro Herrera wrote:
> > Darcy Buskermolen wrote:
> >> On October 31, 2006 08:53 am, Teodor Sigaev wrote:
> >>>> The problem as I remember it is pg_tgrm not tsearch2 directly, I've sent
> >>>> a self contained test case directly to  Teodor  which shows the error.
> >>>>
> >>>> 'ERROR:  index row requires 8792 bytes, maximum size is 8191'
> >>> Uh, I see. But I'm really surprised why do you use pg_trgm on big text?
> >>> pg_trgm is designed to find similar words and use technique known as
> >>> trigrams. This will work good on small pieces of text such as words or set
> >>> expression. But all big texts (on the same language) will be similar :(.
> >>> So, I didn't take care about guarantee that index tuple's size limitation.
> >>> In principle, it's possible to modify pg_trgm to have such guarantee, but
> >>> index becomes lossy - all tuples gotten  from index should be checked by
> >>> table's tuple evaluation.
> >> The problem is some of the data we are working with is not strictly "text" but
> >> bytea that we've run through encode(bytea, 'escape'),
> >
> > I think one good question is why are you storing bytea and then
> > searching like it were text.
>
> We are not storing bytea, a customer is. We are trying to work around
> customer requirements. The data that is being stored is not always text,
> sometimes it is binary (a flash file or jpeg). We are using escaped text
> to be able to search the string contents of that file .

Hmm, have you tried to create a functional trigram index on the
equivalent of "strings(bytea_column)" or something like that?

I imagine strings(bytea) would be a function that returns the
concatenation of all pure (7 bit) ASCII strings in the byte sequence.

On the other hand, based on Teodor's comment on pg_trgm, maybe this
won't be possible at all.

> > Why not store the text as text, and put
> > the extraneous bytes somewhere else?  Certainly you wouldn't expect to
> > be able to find text among the bytes, would you?
>
> Yes we do (and can) expect to find text among the bytes. We have
> searches running, we are just running into the maximum size issues for
> certain rows.

Do you mean you actually find stuff based on text attributes in JPEG
images and the like?  I thought those were compressed ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.