Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-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

pgsql-performance by date

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

pgsql-hackers by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group