Re: Index slow down insertions...

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Ioannis Anagnostopoulos" <ioannis(at)anatec(dot)com>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index slow down insertions...
Date: 2012-07-30 22:43:13
Message-ID: 5016C7B1020000250004928E@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-performance

Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com> wrote:

> I have stripped completely the database from additional indexes,
> those that possible delay the insertion process, of course
> maintaining the pkey and 2 or three absolutely mandatory indexes
> for my select queries. As a result I have a sleek and steady
> performance of around 0.70 msec per insertion.

Not bad!

> However I have now closed a full circle as I have a fast database
> but when I try to "select", making optimum usage of the left over
> indexes, the insertion process slows down. Yes my selections are
> huge (they are not slow, just huge as it is about geographical
> points etc) but I am asking if there is anyway that I can
> "prioritise" the insertions over the "selections". These
> "selections" are happening anyway as batch process during night so
> I don't really mind if they will take 2 or 5 hours, as long as
> they are ready at 9.00am next day.

You could try adding back indexes on the most critical columns, one
at a time. You might want to try single-column indexes, rather than
the wide ones you had before. The narrower keys may cut the cost of
maintaining the indexes enough to tolerate a few, and PostgreSQL can
often combine multiple indexes using "bitmap index scans".

You could also play with "nice" and "ionice" to reduce priority of
the "select" processes, but watch any such attempt very carefully
until you see what the impact really is.

Since you seem to be relatively satisfied with where you are now,
you should make small changes and be prepared to revert them if
insert performance drops off too much.

-Kevin

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2012-07-30 23:15:42 Re: WAL tuning advice
Previous Message Ilija Vidoevski 2012-07-30 20:17:05 Re: very slow update query

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Kirkwood 2012-07-31 01:21:04 Re: Postgres 9.1.4 - high stats collector IO usage
Previous Message Bruce Momjian 2012-07-30 17:26:58 Re: Linux memory zone reclaim