From: Tom Lane <tgl@sss.pgh.pa.us>
To: Saadat Anwar <sanwar@asu.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Copy performance issues
Date: Wed, 18 Aug 2010 18:42:14 -0400
Message-ID: <15118.1282171334@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTi=y+JPmuqU5gAX=gtMTJRcNDGDToa61QQVtAbF0@mail.gmail.com>
References: <AANLkTi=y+JPmuqU5gAX=gtMTJRcNDGDToa61QQVtAbF0@mail.gmail.com>
Saadat Anwar <sanwar@asu.edu> writes:
> I am having severe COPY performance issues after adding indices. What used
> to take a few minutes (without indices) now takes several hours (with
> indices). I've tried to tweak the database configuration (based on Postgres
> documentation and forums), but it hasn't helped as yet. Perhaps, I haven't
> increased the limits sufficiently. Dropping and recreating indices may not
> be an option due to a long time it takes to rebuild all indices.
I suspect your problem is basically that the index updates require a
working set larger than available RAM, so the machine spends all its
time shuffling index pages in and out. Can you reorder the input so
that there's more locality of reference in the index values?
Also, my first reaction to that schema is to wonder whether the lat/lon
indexes are worth anything. What sort of queries are you using them
for, and have you considered an rtree/gist index instead?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sanwar@asu.edu
Subject: Re: Copy performance issues
In-Reply-To: <15118.1282171334@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox