Thread: 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
Janning Vygen <vygen@planwerk6.de> 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
Tom Lane wrote: > Janning Vygen <vygen@planwerk6.de> 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
Am Montag, 16. Juli 2007 16:19 schrieben Sie: > Janning Vygen <vygen@planwerk6.de> 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
mljv@planwerk6.de 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
On Tuesday 17 July 2007 17:47:01 Tom Lane wrote: > mljv@planwerk6.de 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 07/17/07 17:12, mljv@planwerk6.de wrote: > On Tuesday 17 July 2007 17:47:01 Tom Lane wrote: >> mljv@planwerk6.de 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-----