[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Performance problem with large resultsets (byte array 2200)
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2007-07-31 14:25:45
Message-ID: 19283.1185891945 () sss ! pgh ! pa ! us
[Download RAW message or body]

"Victor Adolfsson" <victor@optimumbiometrics.com> writes:
> I'm having a problem with bad performance when retrieving many rows where
> each row has a 2200 long byte array (called template_compressed) and a 50
> character varying (called uniqueid)

2200 bytes is (just) long enough to trigger toasting of the entries, and
I suppose it's the extra time to fetch a toasted value that's hurting.
As long as the total row width is under 8K, you could disable toasting
by ALTER TABLE ... SET STORAGE PLAIN on all the variable-width columns
of the table.  Note that this wouldn't in itself de-toast rows already
stored; so you'd have to dump/truncate/reload the table before seeing
any performance benefit.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
[prev in list] [next in list] [prev in thread] [next in thread]