On Wed, Aug 18, 2010 at 3:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Saadat Anwar <sanwar(at)asu(dot)edu> writes:
> > I am having severe COPY performance issues after adding indices. What
> > to take a few minutes (without indices) now takes several hours (with
> > indices). I've tried to tweak the database configuration (based on
> > documentation and forums), but it hasn't helped as yet. Perhaps, I
> > increased the limits sufficiently. Dropping and recreating indices may
> > 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?
> I can potentially reorder the data so that it has locality of reference
w.r.t. one index, but not all. Or did I not interpret your response
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?
> I always assumed that the btree indices on individual fields were smaller
and more efficient as compared to the rtree/gist indices. Is that not the
case? And since the users did not need points and point-queries, I decided
in the favor of indexing individual fields.
> regards, tom lane
In response to
pgsql-performance by date
|Next:||From: Samuel Gendler||Date: 2010-08-19 05:23:52|
|Subject: in-memory sorting|
|Previous:||From: Greg Smith||Date: 2010-08-18 22:49:50|
|Subject: Re: Copy performance issues|