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