Re: [HACKERS] Another Idea: Try Including snapshot with TOAS (was: Including Snapshot Info with Indexes)
On Sat, 2007-10-13 at 11:12 +0530, Gokulakannan Somasundaram wrote: b) But i don't understand how storing the visibility info in TOAST table would help you. In that case you may need to update it for every delete/ update happening in the main table. Only thing, it would help is if someone wants to do a full table scan on TOAST table alone. May be Vacuum of TOAST tables can be done independently. But do you think it is worth the loss of performance in Update/Delete? No -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Another Idea: Try Including snapshot with TOAS (was: Including Snapshot Info with Indexes)
Hi Simon/Hannu, a) I accept with storing ctid in place of oid. That would definitely improve the performance. I guess it would also remove the restriction of the size on TOASTABLE ATTRIBUTES. I guess different chunks have to be linked together, without the index. b) But i don't understand how storing the visibility info in TOAST table would help you. In that case you may need to update it for every delete/ update happening in the main table. Only thing, it would help is if someone wants to do a full table scan on TOAST table alone. May be Vacuum of TOAST tables can be done independently. But do you think it is worth the loss of performance in Update/Delete? Thanks, Gokul. On 10/8/07, Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2007-10-08 at 14:58 +0300, Hannu Krosing wrote: 1. get rid of indexes for TOAST tables instead of TOAST tuple id store CTID of first TOAST block directly, and use something like skip lists inside the TOAST block headers to access next TOAST tuples. It should be possible to optimise TOAST for when there is just a single chunk that is toasted. Since we often (and by default) compress data stored in TOAST tables this would be a frequently used optimisation. Instead of storing the TOAST OID, which is then looked-up in the index to find the TOAST tid, we can just store the tid directly in the toast pointer on the main heap. That way we'll get faster read and write access for a good proportion of rows by avoiding the toast index and going straight to the toast heap. We'd need a different kind of toast pointer which would be 2 bytes longer than the normal pointer. I think we have a spare flag bit to indicate this. That's just a rough sketch, I've not checked the details on this. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
[HACKERS] Another Idea: Try Including snapshot with TOAS (was: Including Snapshot Info with Indexes)
Ühel kenal päeval, E, 2007-10-08 kell 12:12, kirjutas Gokulakannan Somasundaram: Hi, Currently The index implementation in Postgresql does not store the Snapshot information in the Index. .. Please reply back with your comments. I think you got enoght search for previous discussion answers on this aone already ;) So I propose a few another ideas to investigate 1. get rid of indexes for TOAST tables instead of TOAST tuple id store CTID of first TOAST block directly, and use something like skip lists inside the TOAST block headers to access next TOAST tuples. 2. store visibility info in TOAST tables if you store visibility info in TOAST tuples, it becomes possible to update just the small part of the tuple in the main heap and keep the bulk of toasted data in place. both of these ideas are much more complicated to implement than it appears from my simple description, but should have big benefits for a sizable number of scenarios which use TOAST. - Hannu ---(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] Another Idea: Try Including snapshot with TOAS (was: Including Snapshot Info with Indexes)
On Mon, 2007-10-08 at 14:58 +0300, Hannu Krosing wrote: 1. get rid of indexes for TOAST tables instead of TOAST tuple id store CTID of first TOAST block directly, and use something like skip lists inside the TOAST block headers to access next TOAST tuples. It should be possible to optimise TOAST for when there is just a single chunk that is toasted. Since we often (and by default) compress data stored in TOAST tables this would be a frequently used optimisation. Instead of storing the TOAST OID, which is then looked-up in the index to find the TOAST tid, we can just store the tid directly in the toast pointer on the main heap. That way we'll get faster read and write access for a good proportion of rows by avoiding the toast index and going straight to the toast heap. We'd need a different kind of toast pointer which would be 2 bytes longer than the normal pointer. I think we have a spare flag bit to indicate this. That's just a rough sketch, I've not checked the details on this. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
