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

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 (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-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

pgsql-novice by date

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

pgsql-performance by date

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

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