Re: Patch queue triage - Mailing list pgsql-hackers

Gregory Stark wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>
> > Bruce Momjian <bruce@momjian.us> writes:
> >> FYI, Tom, Heikki, I need one of you to post the list of patches and
> >> where we think we are on each one, even if the list is imperfect.
> >
> > This message is an attempt to sort out which patch queue entries have
> > no hope of getting into 8.3 (and so we shouldn't spend more time on them
> > right now), which ones can get in but are waiting on their authors for
> > more work, and which ones are ready for immediate review.
>
> Thanks for this. This is exactly what we've been missing recently I think.

100% agree.

> > * Re: [HACKERS] Modifying TOAST thresholds  /Tom Lane/
> >
> > At this point it seems nothing will be done about this issue for 8.3.
>
> I'm not sure anyone has an idea how to test it. TPCC isn't really useful
> because it has a fixed size (500 byte) string buffer. Perhaps if we modified
> it to have a random string length uniformly distributed between 0-2k ? But
> even then it never does any scans based on that buffer. But the problem with
> going with something more natural is that it'll be harder to tell exactly what
> it's testing.

My idea on this was to create two backends, one with the default TOAST
value, and a second with a value of 50 bytes.  Create a table with one
TEXT field, and several other columns, each column < 50 bytes.

Then, fill the table with random data (script attached that might help),
and the try 2000, 1500, 1000, etc, bytes in the TEXT column for each row
(use random data so the compression code doesn't shrink it).  Then run a
test with both backends acessing the TEXT column and non-TEXT column and
measure the difference between the two backends, i.e. the backend with a
TOAST value of 50 should show faster access on the non-TEXT field, but
slower access on the TEXT field.

Then, figure out where the gains on the non-TEXT field seem to diminish
in usefulness.  Basically, with a lower TOAST value, we are going to
spend more time accessing the TEXT field, but the speedup for the
non-TEXT field should be large enough win that we don't care. As the
TEXT column becomes shorter, it has less affect on the non-TEXT access.

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

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

ROWS=100000
COLSIZE=2500

echo "
    DROP TABLE test;
    CREATE TABLE test(i SERIAL, t text);
    INSERT INTO test (t)
    SELECT    array_to_string(ARRAY(
        SELECT chr(32 + (95 * random())::integer)
        FROM generate_series(1,$COLSIZE)),'')
    FROM    generate_series(1, $ROWS);
    SELECT     pg_relation_size('test') AS "HEAP",
        pg_total_relation_size('test') - pg_relation_size('test') AS "TOAST";
    SET log_min_duration_statement = 0;
    SET client_min_messages = 'log';
    SELECT t FROM test WHERE i = 234329823;" |
sql test