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