To summarize this thread:
We have tried most of the suggestions and found two of them effective:
1) collapsing OR expressions in the WHERE clause into one '(...)|(...)' regexp resulted in about 60% better search time
2) changing long attribute storage to EXTERNAL gave 30% better search time (but only on the first search - i.e. before data is cached)
Surprisingly, changing shared_mem from 24MB to 1 GB gave no apparent effect.
Thanks once again for all your help!!!
Regards,
Greg
On Thu, Aug 11, 2011 at 3:56 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> A small followup regarding the suggestion to turn off compression
> - I used:
>
> ALTER TABLE archive_tender ALTER COLUMN subject SET STORAGE
> EXTERNAL
>
> to turn off compression, however I get an impression that "nothing
> happend". When exactly this alteration takes effect? Perhaps I
> should reload the entire db from backup to change the storage
> method?
Yeah, the storage option just affects future storage of values; it
does not perform a conversion automatically. There are various ways
you could cause the rows to be re-written so that they use the new
TOAST policy for the column. One of the simplest would be to do a
data-only dump of the table, truncate the table, and restore the
data. If that table is a big enough portion of the database, a
pg_dump of the whole database might be about as simple.
-Kevin