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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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