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: (view raw, whole thread or download thread mbox)
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:

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:
> 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.
> 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
> ---------------------------(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>

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

In response to


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-2017 The PostgreSQL Global Development Group