Re: [GENERAL] createing indexes on large tables and int8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/17/07 17:12, [EMAIL PROTECTED] wrote: On Tuesday 17 July 2007 17:47:01 Tom Lane wrote: [EMAIL PROTECTED] writes: i think i got it fixed as i saw that i pushed my maintenance_work_mem too high. It was higher than physical ram :-( Ooops, that will definitely cause problems. yes it did! I ran it again. And now it takes 10 minutes per index instead of 10 hours (still 8.1). maybe something postgres should complain about if setting maintance_work_mem too high. Unless it does some really OS-specific calls, *can* PostgreSQL know how much *physical* RAM is in a box? - -- 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) iD8DBQFGnk+zS9HxQb37XmcRAsDtAKCCadB0CF8ATeHCtO79wcTD3lER7wCgttoF E9Rndryd/IhZEP2FY7yIr/A= =bDSf -END PGP SIGNATURE- ---(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] createing indexes on large tables and int8
Am Montag, 16. Juli 2007 16:19 schrieben Sie: Janning Vygen [EMAIL PROTECTED] writes: After this i create the index and it took 10 hours just for one index (primary key). I have 100.000.000 rows with one PK (int8), two integer data values, and two FK (int8) What PG version is this? We did a fair amount of work on sort speed for 8.2. sorry for forgetting the version number. it is 8.1. i think i got it fixed as i saw that i pushed my maintenance_work_mem too high. It was higher than physical ram :-( i don't know what happens but i guess it is swapping all the time i will try it again - with correct maintenance_work_mem setting - with 8.2 as your statement sounds great. i never thought pg8.1 could be improved anyway as i was already totally satisfied with its performance. i will ask my second question in a different thread. kind regards, janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] createing indexes on large tables and int8
[EMAIL PROTECTED] writes: i think i got it fixed as i saw that i pushed my maintenance_work_mem too high. It was higher than physical ram :-( Ooops, that will definitely cause problems. 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
Re: [GENERAL] createing indexes on large tables and int8
On Tuesday 17 July 2007 17:47:01 Tom Lane wrote: [EMAIL PROTECTED] writes: i think i got it fixed as i saw that i pushed my maintenance_work_mem too high. It was higher than physical ram :-( Ooops, that will definitely cause problems. yes it did! I ran it again. And now it takes 10 minutes per index instead of 10 hours (still 8.1). maybe something postgres should complain about if setting maintance_work_mem too high. Thanks for your help. kind regards janning ---(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: [GENERAL] createing indexes on large tables and int8
Janning Vygen [EMAIL PROTECTED] writes: After this i create the index and it took 10 hours just for one index (primary key). I have 100.000.000 rows with one PK (int8), two integer data values, and two FK (int8) What PG version is this? We did a fair amount of work on sort speed for 8.2. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] createing indexes on large tables and int8
Tom Lane wrote: Janning Vygen [EMAIL PROTECTED] writes: After this i create the index and it took 10 hours just for one index (primary key). I have 100.000.000 rows with one PK (int8), two integer data values, and two FK (int8) What PG version is this? We did a fair amount of work on sort speed for 8.2. yeah - back when i tested that during the 8.2 development cycle I got a 5-6x speedup with the external sort improvements. ie sorting 1.8B rows (integer) went down from over 12h to about 2h10min - but 10h sounds like a lot for only 100M rows - I wonder what kind of hardware that is and how much concurrent activity is going on ... Stefan ---(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
