Re: Problem search on text arrays, using the overlaps (&&) operator

Hi Andreas,

I'm afraid fulltext search won't fit our app here. Our application
tags each record with "source flags", which is a text[] of strings
that describes where the record came from. These flags are already
passed into the application when we store the records. So we can
simply store them as text[]. Contrast to this, doing a fulltext search
would be storing these flags as one single string, then using the
to_tsvector() to have PostgreSQL parse it out again. The fulltext
search approach doesn't seem to make sense for us.

I'm also suspcious that the same type of problem would affect queries
on tsvector columns, but I have not tested myself.

----- Original Message -----
From: "Andreas Wenk" <a(dot)wenk(at)netzmeister-st-pauli(dot)de>
To: "John Cheng" <jlcheng(at)ymail(dot)com>, "PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, July 3, 2009 2:12:46 AM GMT -08:00 US/Canada Pacific
Subject: Re: [GENERAL] Problem search on text arrays, using the overlaps (&&) operator

John Cheng schrieb:
> -----------
> For some reason, I am seeing a big difference in our real database. I
> don't want to just rewrite all of our queries yet. I'm guessing the
> data makes a big difference. What would be a good way to examine the
> data to figure out what's the best way to write our queries? Is there
> any features in PostgreSQL that can help me improve the performance?
>
> Any advice would be greatly appreciated!

Hi,

did you think about using the fulltext search integrated up from version 8.3. I never used
your approach and don't know if the fulltextsearch is suitable for your case ... just a hint.

http://www.postgresql.org/docs/8.4/interactive/textsearch.html

Cheers

Andy