Re: Fwd: Performance Tuning

On Mon, Nov 17, 2008 at 11:21 AM, John Zhang <johnzhang06(at)gmail(dot)com> wrote:
>
>
> Hi the list,
>
> I have a performance problem and would like to any input on how to make it
> perform as desired. In the DB, there are a few tables over 3 million records
> in postgis. When I do some operation on the tables, e.g. CREATE INDEX, it
> takes hours without results. I believe there must be something wrong within
> the DB. However, I have not figured it out. Could anyone point me to the
> right direction to tune and configured the database efficiently?

Assuming it's creating GIN indexes, and possibly even if not, index
creation is very dependent on having a large enough work_mem for it to
fit a large portion of the data it's working on in memory. Try
cranking up work_mem before index creation. Note that you don't have
to do this in postgresql.conf and reload, you can just do it in the
session you're in:

set work_mem=512000;
create index blah blah blah;