Re: poor pefrormance with regexp searches on large tables

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?

Regards,

greg

On Wed, Aug 10, 2011 at 7:17 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Grzegorz Blinowski <g(dot)blinowski(at)gmail(dot)com> wrote:
>
> > the problem is not disk transfer/access but rather the way
> > Postgres handles regexp queries.
>
> As a diagnostic step, could you figure out some non-regexp way to
> select about the same percentage of rows with about the same
> distribution across the table, and compare times? So far I haven't
> seen any real indication that the time is spent in evaluating the
> regular expressions, versus just loading pages from the OS into
> shared buffers and picking out individual tuples and columns from
> the table. For all we know, the time is mostly spent decompressing
> the 2K values. Perhaps you need to save them without compression.
> If they are big enough after compression to be stored out-of-line by
> default, you might want to experiment with having them in-line in
> the tuple.
>
> http://www.postgresql.org/docs/8.4/interactive/storage-toast.html
>
> -Kevin
>