Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Hi, As said, i am attaching the performance test results and the same patch in this thread works with the latest CVS head. Actually, i am seeing a slight performance improvement with the patch, which i think might be either because of noise/ lesser pages. i ran it with the default settings. i have tested only inserts and selects, because that's where the code change has happened. Regarding Tom's comments As far as the changes are concerned, the patch changes the following functions a) heap_fill_tuple b) nocachegetattr c) heap_form_tuple d) index_form_tuple e) nocache_index_getattr f) changed the macros index_getattr, IndexTupleSize, IndexTupleDSize g) Introduced a new macro IndexTupleActualSize The patch introduces the following changes to the storage of tuples 1) If there are only trailing nulls, it doesn't store the null bitmap 2) If there are non-trailing nulls and trailing nulls, it stores the null-bitmap only till the last non-null value. so it decreases the storage requirement of null bitmap. This is expected to have only very few use-cases 3) It doesn't store the null-bitmap for trailing nulls in indexes also The functions mentioned in d), e), f), g) are required for the functionality of index null-bitmap handling. I suppose, we can't handle it with only heap_form_tuple. Please correct me, if i am wrong.. For having the functionality 2), we have to touch the heap_fill_tuple. i have done the trick, by asking it to use the passed number of attributes, instead of taking it from tupdesc. Again please advice me on how to implement this with only heap_form_tuple. Looking forward for comments/suggestions. Thanks, Gokul. Trailing null - results.ods Description: application/vnd.oasis.opendocument.spreadsheet -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
I would work on this and try to present the performance test results. I would also go ahead and examine, whether the logic can be added into heap_form_tuple by any means. Thanks, Gokul. On Wed, Mar 19, 2008 at 12:11 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: * Consider not storing a NULL bitmap on disk if all the NULLs are trailing http://archives.postgresql.org/pgsql-hackers/2007-12/msg00624.php http://archives.postgresql.org/pgsql-patches/2007-12/msg00109.php Tom's comments are: What this lacks is some performance testing to measure the cost of the extra tests in heap_form_tuple. If that can be shown to be negligible then it's probably worth doing though I don't like any part of the actually submitted patch ;-). All this should need is a bit more logic in heap_form_tuple and heap_formtuple. --- Gokulakannan Somasundaram wrote: Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Added to TODO: * Consider not storing a NULL bitmap on disk if all the NULLs are trailing http://archives.postgresql.org/pgsql-hackers/2007-12/msg00624.php http://archives.postgresql.org/pgsql-patches/2007-12/msg00109.php Tom's comments are: What this lacks is some performance testing to measure the cost of the extra tests in heap_form_tuple. If that can be shown to be negligible then it's probably worth doing though I don't like any part of the actually submitted patch ;-). All this should need is a bit more logic in heap_form_tuple and heap_formtuple. --- Gokulakannan Somasundaram wrote: Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
We can also implement the same for index tuples. On Dec 17, 2007 1:10 PM, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com) -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. I think that would work. The only question is whether it's worth bothering since we would have to check it on every heap_form_tuple. But I suspect it might be possible to do it pretty cheaply or perhaps even for free. The extra complexity would be pretty localized so I don't think that's a big downside. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. Actually this already works. *_deform_tuple has to be able to deal with tables to which people have added columns. In that case tuples inserted before the columns were added will look just as you describe, with trailing columns missing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
On Mon, 2007-12-17 at 13:10 +0530, Gokulakannan Somasundaram wrote: Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is Will this work for ALTER TABLE when adding and dropping columns? Another idea is to store the bitmap from the first nullable column. Some of these ideas have been discussed before, so I would check the archives thoroughly. Most everything has if you look closely enough. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
On Dec 17, 2007 3:28 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2007-12-17 at 13:10 +0530, Gokulakannan Somasundaram wrote: Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is Will this work for ALTER TABLE when adding and dropping columns? When we drop columns, it is not at all an issue. When we add columns, by default they have null values. If we want to set default, postgres allows it only for new inserts. Can you think of any specific instance. Another idea is to store the bitmap from the first nullable column. This is a different idea. I like this. I will think about this also. Some of these ideas have been discussed before, so I would check the archives thoroughly. Most everything has if you look closely enough. I have done a fair amount of search in the archives. But if you remember any please notify me about it. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Thanks. I agree with you. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Gregory Stark wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. I think that would work. The only question is whether it's worth bothering since we would have to check it on every heap_form_tuple. This strikes me as such a corner case that it's likely not to be worth it. If you really want to save space along these lines, one better place to start might be mutable with column ordering - see http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php . That would mean that we would be able to move nullable columns physically to the tail which in turn might help this suggestion have more effect. cheers andrew ---(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
Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
On Mon, 2007-12-17 at 08:47 -0500, Andrew Dunstan wrote: This strikes me as such a corner case that it's likely not to be worth it. If you really want to save space along these lines, one better place to start might be mutable with column ordering - see http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php . That would mean that we would be able to move nullable columns physically to the tail which in turn might help this suggestion have more effect. Could be a good idea. Currently on a 64-bit system we occupy 23 bytes for row header, so any table with more than 8 columns will cause the null bitmap to overflow and for us to use another 8 bytes. OP's idea could avoid that in many cases, so the saving isn't 1 byte it is fairly frequently going to be an 8 byte saving. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
