Skip site navigation (1) Skip section navigation (2)

Re: TOAST usage setting

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Zeugswetter Andreas ADI SD <ZeugswetterA(at)spardat(dot)at>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: TOAST usage setting
Date: 2007-05-31 02:08:56
Message-ID: 200705310208.l4V28uo26381@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I tested EXTERN_TUPLES_PER_PAGE for values 4(default), 2, and 1:

	4	15.596
	2	15.197
	1	14.6

which is basically a 3% decrease from 4->2 and 2->1.  The test script
and result are here:

	http://momjian.us/expire/TOAST2/

shared_buffers again was 32MB so all the data was in memory.

---------------------------------------------------------------------------

Gregory Stark wrote:
> 
> "Bruce Momjian" <bruce(at)momjian(dot)us> writes:
> 
> > Uh, am I supposed to be running more TOAST tests?  Would someone explain
> > what they want tested?
> 
> If you want my opinion I would say we need two tests:
> 
> 1) For TOAST_TUPLE_TARGET:
> 
> We need to run the test scripts you have already for sizes that cause actual
> disk i/o. The real cost of TOAST lies in the random access seeks and your
> tests all fit in memory so they're missing that.
> 
> 2) And for TOAST_MAX_CHUNK_SIZE:
> 
> Set TOAST_MAX_CHUNK_SIZE to 8k and TOAST_TOAST_TUPLE_TARGET to 4097 and store
> a large table (larger than RAM) of 4069 bytes (and verify that that's creating
> two chunks for each tuple). Test how long it takes to do a sequential scan
> with hashtext(). Compare that to the above with TOAST_MAX_CHUNK_SIZE set to 4k
> (and verify that the toast table is much smaller in this configuration).
> 
> Actually I think we need to do the latter of these first. Because if it shows
> that bloating the toast table is faster than chopping up data into finer
> chunks then we'll want to set TOAST_MAX_CHUNK_SIZE to 8k and then your tests
> above will have to be rerun.
> 
> -- 
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

Responses

pgsql-hackers by date

Next:From: OmniPay CompanyDate: 2007-05-31 02:42:41
Subject: Job Proposal
Previous:From: Josh BerkusDate: 2007-05-31 01:03:24
Subject: Re: Query plan degradation 8.2 --> 8.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group