Ioannis Anagnostopoulos <ioannis@anatec.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