Re: Re: patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap [Review]

On Thursday, December 20, 2012 5:46 PM Simon Riggs wrote:
> On 13 October 2012 08:54, Amit kapila <amit.kapila@huawei.com> wrote:
> 
> > As per the last discussion for this patch, performance data needs to
> be
> > provided before this patch's Review can proceed further.
> >
> > So as per your suggestion and from the discussions about this patch,
> I have

> >
> > ------------------------------------------------
> >
> > 1. There is no performance change for cloumns that have all valid
> > values(non- NULLs).
> >
> > 2. There is a visible performance increase when number of columns
> containing
> > NULLS are more than > 60~70% in table have large number of columns.
> >
> > 3. There are visible space savings when number of columns containing
> NULLS
> > are more than > 60~70% in table have large number of columns.
> >
> >
> > Let me know if there is more performance data needs to be collected
> for this
> > patch?
> 
> 
> I can't make sense of your performance report. Because of that I can't
> derive the same conclusions from it you do.
> 
> Can you explain the performance results in more detail, so we can see
> what they mean? Like which are the patched, which are the unpatched
> results? 
On the extreme let it is mentioned Original Code/ Trim Triling Nulls Patch.
In any case I have framed the results again as below:
1. Table with 800 columns 
A. INSERT tuples with 600 trailing nulls 
B. UPDATE last column value to "non-null" 
C. UPDATE last column value to "null" 
---------------------+---------------------+---------------------     Original Code   |  Trim Tailing NULLs |
Improvement(%)       TPS   space used|  TPS    space used  | Results           (pages)   |         (pages)     |     
 
---------------------+---------------------+---------------------- 
1A:  0.2068  250000  | 0.2302  222223      | 10.1% tps, 11.1% space 
1B:  0.0448  500000  | 0.0481  472223      |  6.8% tps,  5.6% space 
1C:  0.0433  750000  | 0.0493  694445      | 12.2% tps,  7.4% space 

2. Table with 800 columns 
A. INSERT tuples with 300 trailing nulls 
B. UPDATE last column value to "non-null" 
C. UPDATE last column value to "null" 
---------------------+---------------------+---------------------     Original Code   |  Trim Tailing NULLs |
Improvement(%)       TPS   space used|  TPS    space used  | Results           (pages)   |         (pages)     |     
 
---------------------+---------------------+---------------------- 
2A:  0.0280   666667 | 0.0287   666667     | 2.3% tps, 0% space 
2B:  0.0143  1333334 | 0.0152  1333334     | 5.3% tps, 0% space 
2C:  0.0145  2000000 | 0.0149  2000000     | 2.9% tps, 0% space 

3. Table with 300 columns 
A. INSERT tuples with 150 trailing nulls 
B. UPDATE last column value to "non-null" 
C. UPDATE last column value to "null" 
---------------------+---------------------+--------------------     Original Code   |  Trim Tailing NULLs |
Improvement(%)       TPS   space used|  TPS    space used  | Results           (pages)   |         (pages)     |     
 
---------------------+---------------------+-------------------- 
3A:  0.2815  166667  | 0.2899  166667      | 2.9% tps, 0% space 
3B:  0.0851  333334  | 0.0870  333334      | 2.2% tps, 0% space 
3C:  0.0846  500000  | 0.0852  500000      | 0.7% tps, 0% space 

4. Table with 300 columns 
A. INSERT tuples with 250 trailing nulls 
B. UPDATE last column value to "non-null" 
C. UPDATE last column value to "null" 
---------------------+---------------------+-------------------------     Original Code   |  Trim Tailing NULLs |
Improvement(%)       TPS   space used|  TPS    space used  | Results           (pages)   |         (pages)     |     
 
---------------------+---------------------+------------------------- 
4A:  0.5447    66667 | 0.5996   58824      |  09.2% tps, 11.8% space 
4B:  0.1251   135633 | 0.1232  127790      | -01.5% tps,  5.8% space 
4C:  0.1223   202299 | 0.1361  186613      |  10.1% tps,  7.5% space

Please let me know, if still it is not clear.

With Regards,
Amit Kapila.