Re: Performance while loading data and indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: shridhar_daithankar(at)persistent(dot)co(dot)in
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: Performance while loading data and indexing
Date: 2002-09-26 14:33:58
Message-ID: 3936.1033050838@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general pgsql-hackers pgsql-performance

"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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2002-09-26 14:42:08 Re: [HACKERS] Performance while loading data and indexing
Previous Message Justin Clift 2002-09-26 09:56:34 Re: [HACKERS] Performance while loading data and indexing

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2002-09-26 14:37:35 Re: Performance while loading data and indexing
Previous Message Curt Sampson 2002-09-26 14:20:52 Re: PGXLOG variable worthwhile?

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2002-09-26 14:38:00 Re: rotatelog / logrotate with PostgreSQL
Previous Message Stephan Szabo 2002-09-26 14:15:57 Re: Adding "on delete cascade" after table creation ?