Re: poor pefrormance with regexp searches on large tables

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(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Grzegorz Blinowski <g(dot)blinowski(at)gmail(dot)com> 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
>