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

On Sunday, December 23, 2012 8:11 PM Simon Riggs wrote:
> On 20 December 2012 14:56, Amit Kapila <amit.kapila@huawei.com> wrote:
> 
> >> > 1. There is no performance change for cloumns that have all valid
> >> > values(non- NULLs).
> 
> I don't see any tests (at all) that measure this.
> 
> I'm particularly interested in lower numbers of columns, so we can
> show no regression for the common case.

For now I have taken for 300 columns, I can take for 10~30 columns reading
as well if required

1. Table with 300 columns (all integer columns) 
A. INSERT tuples without trailing nulls 
B. UPDATE last column value to "null" 
----------------+---------------------+------------------ 
Original Code   |  Trim Tailing NULLs | Improvement (%)       TPS        |  TPS                | Results 
----------------+---------------------+------------------ 
1A: 0.1348      | 0.1352              |  0.3%   
1B: 0.0495      | 0.0495              |  0.0%

> >> > 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.
> 
> Agreed.
> 
> I would call that quite disappointing though and was expecting better.
> Are we sure the patch works and the tests are correct?
> 
> The lack of any space saving for lower % values is strange and
> somewhat worrying. There should be a 36? byte saving for 300 null
> columns in an 800 column table - how does that not show up at all?

300 NULL's case will save approximately 108 bytes, as 3 tuples will be
accommodated in such case.
So now the total space left in page will be approximately 1900 bytes
(including 108 bytes saved by optimization).
Now the point is that in existing test case all rows are same (approx 2100
bytes), so no space saving is shown, but incase the last row is such that it
can get accommodated in space saved (remaining space of page + space saved
due to NULLS optimization), then it can show space savings as well.

In anycase there is a performance gain for 300 NULLS case as well.

Apart from above, the performance data for less number of columns (where the
trailing nulls are such that they cross word boundary) also show similar
gains:

The below cases (2 & 3) can give benefit as it will save 4 bytes per tuple

2. Table with 12 columns (first 3 integer followed by 9 Boolean columns) 
A. INSERT tuples with 9 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.8485   12739  |  0.8524   10811     |  0.4%  15.1% 
2B:  0.5847   25478  |  0.5749   23550     | -1.5%   7.5% 
2C:  0.5591   38217  |  0.5545   34361     |  0.8%  10.0% 


3. Table with 12 columns  (first 3 integer followed by 9 Boolean columns) 
A. INSERT tuples with 4 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.8443   14706  |  0.8626   12739     |  2.3%  13.3% 
3B:  0.5307   29412  |  0.5272   27445     | -0.6%   6.7% 
3C:  0.5102   44118  |  0.5218   40184     |  2.2%   8.9%

As a conclusion point, I would like to say that this patch doesn't have
performance regression for most used scenario's
and it gives benefit in some of the trailing null's cases.  

With Regards,
Amit Kapila.