Re: [PATCHES] table/index fillfactor control, try 2
Simon Riggs <[EMAIL PROTECTED]> writes: > This was a response to Itagaki's comment that there may be a class of > parameter that changes more frequently than that. I can see that we > might want that, so just trying to plan ahead to > dynamically/automatically set parameters - I thought you'd be in favour > of that rather than lots of hand-tuned static parameters for indexes. Anything that's automatically set can be completely hidden within the index AM, no? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] table/index fillfactor control, try 2
On Mon, 2006-06-19 at 09:15 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Looks like we've got a class of data that is similar in its frequency of > > change to the pg_class_nt stuff. > > Say what? These parameters wouldn't ever change after creation, unless > we invent ALTER commands to change them. This was a response to Itagaki's comment that there may be a class of parameter that changes more frequently than that. I can see that we might want that, so just trying to plan ahead to dynamically/automatically set parameters - I thought you'd be in favour of that rather than lots of hand-tuned static parameters for indexes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] table/index fillfactor control, try 2
Simon Riggs <[EMAIL PROTECTED]> writes: > Looks like we've got a class of data that is similar in its frequency of > change to the pg_class_nt stuff. Say what? These parameters wouldn't ever change after creation, unless we invent ALTER commands to change them. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] table/index fillfactor control, try 2
On Mon, 2006-06-19 at 14:36 +0900, ITAGAKI Takahiro wrote: > Simon Riggs <[EMAIL PROTECTED]> wrote: > > > > 2. Store the structures in AM's meta page. But heaps don't have meta > > > pages. > > > > But perhaps they should? That sounds very similar to the idea of > > non-transactional pg_class data. > > Presently, I suppose the parameters are not modified so many times. > They are set only on build time or maintenance. > > I think we will need metapages in the future, but not right now. If we will > provide an automatic configurator for fillfactors (or other parameters), > parameters might be changed every time the configurator runs, for example, > per autovacuum. Yes, I can see that future too. > > The metagpages thought would require some consolidation from various > > other proposals, so we'll need to wait for wider discussion on that. > > I agree, but it is easy to move the metadata from catalog to metapages. > So the metapages thought can be reconciled among proposals that must need it. > (pg_class_nt and dead tuples bitmaps?) I've copied in Alvaro to comment on possible cross-overs between these projects... Looks like we've got a class of data that is similar in its frequency of change to the pg_class_nt stuff. Also, the discussion about having some of this type of info cached in shared memory seems to have dried up. Should we now go for pg_class_nt plus shared memory cache? -- Simon Riggs EnterpriseDB http://www.enterprisedb.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: [PATCHES] table/index fillfactor control, try 2
Simon Riggs <[EMAIL PROTECTED]> wrote: > > 2. Store the structures in AM's meta page. But heaps don't have meta pages. > > But perhaps they should? That sounds very similar to the idea of > non-transactional pg_class data. Presently, I suppose the parameters are not modified so many times. They are set only on build time or maintenance. I think we will need metapages in the future, but not right now. If we will provide an automatic configurator for fillfactors (or other parameters), parameters might be changed every time the configurator runs, for example, per autovacuum. > The metagpages thought would require some consolidation from various > other proposals, so we'll need to wait for wider discussion on that. I agree, but it is easy to move the metadata from catalog to metapages. So the metapages thought can be reconciled among proposals that must need it. (pg_class_nt and dead tuples bitmaps?) --- ITAGAKI Takahiro NTT Open Source Software Center ---(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: [PATCHES] table/index fillfactor control, try 2
Tom Lane <[EMAIL PROTECTED]> wrote: > The disadvantage of putting this stuff into metapages is that then you > need some entirely new protocol for letting clients get at it (and > pg_dump, for one, needs to). > An opaque bytea won't do though. What I'd suggest is something real > close to the format used for GUC parameters in ALTER DATABASE SET and > ALTER USER SET, ie, pairs of keyword/value strings. Ok, I'll consult the ALTER DATABASE SET codes. Storing in arrays might make it difficult to retrieve relations that match conditions specified by clients. However, I think such queirs are not used so many times. And if necessary, we can provide a helper function to extract a value from an array, like "valueof(reloptions, 'fillfactor') > 90" --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] table/index fillfactor control, try 2
Simon Riggs <[EMAIL PROTECTED]> writes: > On Fri, 2006-06-16 at 13:33 +0900, ITAGAKI Takahiro wrote: >> 2. Store the structures in AM's meta page. But heaps don't have meta pages. > But perhaps they should? That sounds very similar to the idea of > non-transactional pg_class data. The disadvantage of putting this stuff into metapages is that then you need some entirely new protocol for letting clients get at it (and pg_dump, for one, needs to). I agree with putting it in a catalog. An opaque bytea won't do though. What I'd suggest is something real close to the format used for GUC parameters in ALTER DATABASE SET and ALTER USER SET, ie, pairs of keyword/value strings. This way pg_dump doesn't need very much smarts about what the values are that it's dumping. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] table/index fillfactor control, try 2
On Fri, 2006-06-16 at 13:33 +0900, ITAGAKI Takahiro wrote: > This is a revised fillfactor patch. It uses WITH syntax and > we can add new AM specific parameters easily. Cool. I'll look at that in more detail. > > So we have a new element of the RelationData struct: > > void*rd_amopts; > > Which each AM defines and interprets. > > The internal structure is stored in the pg_class.relamopaque column as bytea. > I guess it is not the best and there is room for discussion. I examined the > following ideas, but they had complexities and difficulties. > > 1. Add AM specific system tables (pg_heap, pg_btree, etc.) that may inherit > pg_class. But it will impact the current source code terribly. Hmmm, yep, not a good idea. > 2. Store the structures in AM's meta page. But heaps don't have meta pages. But perhaps they should? That sounds very similar to the idea of non-transactional pg_class data. It would make a lot of sense if heaps had meta pages too, and that the data within them was cached on the relcache just as index meta page data will be for 8.2 > 3. Store them into an array of text column that newly added to pg_class. > But we hove to re-parse the array every time relations are loaded. Not sure if thats a big overhead? Is it a big array? I hope not. We should be aiming for as few parameters as possible for an index/heap, otherwise we'll never be able to determine their correct settings. > 4. Add new system table, pg_class_option (relid, option name, value). > But it has same problem as 3 and needs additional heap scannings. No thanks. > Therefore, I choose the as-is binary format to store the internal structures. > Any comments or better ideas? Well, its either metapages or array-on-pg_class for me. The metagpages thought would require some consolidation from various other proposals, so we'll need to wait for wider discussion on that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
