Re: [GENERAL] Feature Request - Defining default table space for Indexes in Conf file
On 10/1/07, S Sharma [EMAIL PROTECTED] wrote: Hi All, The default table space defined in db conf file is used for all database tables as well as indexes. So putting the indexes on another table space requires manually dropping and re-creating indexes. It would be nice to have a feature to define a default table space for indexes in db conf file and all indexed are created in that table space. This would allow creating a good database architecture to avoid disc contention easily. Thanks Data_arch Although the most basic optimization suggested when using tablespaces is always Put indexes on one and data on another to avoid disk contention, I doubt that the ideal optimization for many workloads, which means sticking such a thing in a config file might not be such a good idea. In other words, a DBA probably ought to think harder about optimizing his/her use of tablespaces than just I'll put indexes on this one and data on another. See http://www.depesz.com/index.php/2007/09/30/finding-optimum-tables-placement-in-2-tablespace-situation/ and http://people.planetpostgresql.org/xzilla/ for two recent blog posts on the subject. But now I'll be quiet, because I have no evidence to prove any of the above :) - Josh ---(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: [GENERAL] Feature Request - Defining default table space for Indexes in Conf file
Josh Tolley [EMAIL PROTECTED] writes: On 10/1/07, S Sharma [EMAIL PROTECTED] wrote: It would be nice to have a feature to define a default table space for indexes in db conf file and all indexed are created in that table space. Although the most basic optimization suggested when using tablespaces is always Put indexes on one and data on another to avoid disk contention, I doubt that the ideal optimization for many workloads, which means sticking such a thing in a config file might not be such a good idea. In other words, a DBA probably ought to think harder about optimizing his/her use of tablespaces than just I'll put indexes on this one and data on another. Yeah, I think that argument is why we did not provide such a setup to begin with... regards, tom lane ---(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
[GENERAL] Feature Request - Defining default table space for Indexes in Conf file
Hi All, The default table space defined in db conf file is used for all database tables as well as indexes. So putting the indexes on another table space requires manually dropping and re-creating indexes. It would be nice to have a feature to define a default table space for indexes in db conf file and all indexed are created in that table space. This would allow creating a good database architecture to avoid disc contention easily. Thanks Data_arch - Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
Re: [GENERAL] Feature Request - Defining default table space for Indexes in Conf file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/01/07 13:22, S Sharma wrote: Hi All, The default table space defined in db conf file is used for all database tables as well as indexes. So putting the indexes on another table space requires manually dropping and re-creating indexes. It would be nice to have a feature to define a default table space for indexes in db conf file and all indexed are created in that table space. ALTER INDEX foo SET TABLESPACE bar; This would allow creating a good database architecture to avoid disc contention easily. How difficult is it to specify tablespace when creating an index? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHAnExS9HxQb37XmcRAiceAJ9vUNKVa8voo2gISHhzDgKY4OOkuQCgxuxG jR6S8CY4INa+fKbOE00oqZk= =3QvI -END PGP SIGNATURE- ---(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
