On Wed, Aug 18, 2010 at 3:42 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
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.
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.