Thread: memory paramters for building indexes

Hi,

Thanks for the suggestions provided for my question regarding postgres.config file last time.

I am trying to see how these memory parameters affect time taken to build indexes. I understood that maintenance_work_mem is an important variable for this task. Accordingly, I set it to:
1MB
8MB
16MB
for each of the above value, I created a hash index on a 1.17GB table. I expected that maximun time will be taken when main_work_mem=1MB.  But, surprisingly, time for each of them were:
for 1MB - 1874.072 s = 31.23 minutes
8MB - 1915.403 s = 31.92 minutes
16MB - 31.25 minutes...

i tried it on another table of size 0.21 GB and i dint find much of a difference there as well....it was around 4.6 minutes for all 3 values of main_work_mem.
I restarted the psql server everytime I changed the memory value. In order to make sure these values were considered, I gave an invalid value and the server did not start.

Am i missing something? should i change any other value? any help in this direction will be appreciated.

thanks!
Shreya

---

Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.

Shreya Bhargava <shreya_bhargav@yahoo.com> writes:
> for each of the above value, I created a hash index on a 1.17GB table.

Hash index build doesn't have any need for workspace, so it will not go
faster with larger work_mem.  Try it with a regular btree index.

> Am i missing something?

Have you read the statements in the manual that discourage you from
using hash indexes at all?  Our current implementation of them has
no known advantages over a btree, and several major disadvantages.

            regards, tom lane