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
[GENERAL] createing indexes on large tables and int8
Hi i try to populate a database. I dropped all indexes on the target table to speed up the copy. it works fine. 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) Are there other options than maintenance_work_mem to speed up index creation? How do i find the optimal value for maintenance_work_mem. At the moment i have 160MB of maintenance work_mem. related questions: I use int8 types in most PK or FK columns. I could change my java code to use integer instead of Long ( i dont know why i took Long in the first place). a) Would int4 instead of int8 speed up creation of index? b) it will reduze the size of the table, of course. Would this reduce size of index, too? By the same amount? c) How much speed up will i gain on queries? Postgresql Doc mention it in section data types without saying how much speed-up i gain. Please, i just want to know if its worth it. Is it more like 0,1%, 1%, 10% or 50%? any help on speeding this up is very appreciated. 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
