Re: Copy performance issues

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
> 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?
>
> 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
correctly?

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
>

Thanks.
Saadat.