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