Re: Patch queue triage - Mailing list pgsql-hackers

Another complexity is testing cases where the table fits in shared
memory/RAM, and those that don't, and testing cases where heap fits in
RAM only because we pushed things to TOAST, and cases where we have to
do lots of TOAST access that doesn't fit in RAM.  I wonder if it is even
worth testing it because pushing more to TOAST probably means the more
frequently accessed data is in RAM.

Anyway, how is going to run these tests?

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

Gregory Stark wrote:
> 
> "Bruce Momjian" <bruce@momjian.us> writes:
> 
> > 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.
> 
> I guess the key is to break down what it is we want to measure into several
> parts. These can each be measured precisely for various sized TOASTed data.
> 
> Costs:
> 
> 1) cost of retrieving data from TOAST pointer versus retrieving data from
>    inline tuple. We just want the absolute time difference between the two
>    operations, not the percentage difference.
> 
> 2) cost of creating TOAST pointer (ie, inserting a new tuple with a TOAST
>    pointer or updating a previously inlined tuple to have a TOASTed column). 
> 
> 3) cost of deleting a TOAST pointer (ie, deleting a tuple or updating a tuple
>    to no longer have a TOASTed column)
> 
> 3) cost of deleting a tuple with an existing TOAST pointer (or updating a
>    tuple to be all inlined) versus deleting an plain tuple or updating a plain
>    tuple.
> 
> Savings:
> 
> 1) time savings accessing a tuple without retrieving the TOAST pointer versus
>    having to access the tuple with the data inlined.
> 
> 2) time savings updating a tuple without modifying toasted data versus
>    updating same tuple with the data inlined in both versions.
> 
> The plan you described would be testing costs 1 and savings 1 but I think we
> need to continue to the others as well.
> 
> Then the trick is to somehow make some argument about the frequency of the
> various operations and the acceptable tradeoff. I think you're right that the
> time spent accessing the data would be the most important metric.
> 
> -- 
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--  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. +