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

Re: Copy performance issues

From: s anwar <sanwar(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Copy performance issues
Date: 2010-08-18 22:59:52
Message-ID: AANLkTikwxAPTE7-yT621MAjK-9sgWEHG=+DQgw1OcnFM@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Samuel GendlerDate: 2010-08-19 05:23:52
Subject: in-memory sorting
Previous:From: Greg SmithDate: 2010-08-18 22:49:50
Subject: Re: Copy performance issues

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