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.gritt...@wicourts.gov > wrote: > Grzegorz Blinowski <g.blinow...@gmail.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 >
