Re: poor pefrormance with regexp searches on large tables

[Grzegorz Blinowski <g.blinowski@xxxxxxxxx>]

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.Grittner@xxxxxxxxxxxx> wrote:

Grzegorz Blinowski <g.blinowski@xxxxxxxxx> 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