Re: Performance while loading data and indexing

"Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
> RedHat7.2/PostgreSQL7.1.3

I'd suggest a newer release of Postgres ... 7.1.3 is pretty old ...

> Create unique composite index on 2 char and a timestamp field: 25226 sec.

What do you mean by "char" exactly? If it's really char(N), how much
are you paying in padding space? There are very very few cases where
I'd not say to use varchar(N), or text, instead. Also, does it have to
be character data? If you could use an integer or float datatype
instead the index operations should be faster (though I can't say by
how much). Have you thought carefully about the order in which the
composite index columns are listed?

> sort_mem = 12000

To create an index of this size, you want to push sort_mem as high as it
can go without swapping. 12000 sounds fine for the global setting, but
in the process that will create the index, try setting sort_mem to some
hundreds of megs or even 1Gb. (But be careful: the calculation of space
actually used by CREATE INDEX is off quite a bit in pre-7.3 releases
:-(. You should probably expect the actual process size to grow to two
or three times what you set sort_mem to. Don't let it get so big as to
swap.)

> wal_buffers = 65536

The above is a complete waste of memory space, which would be better
spent on letting the kernel expand its disk cache. There's no reason
for wal_buffers to be more than a few dozen.

regards, tom lane