Re: poor pefrormance with regexp searches on large tables

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