To: Gael Le Mignot <gael@pilotsystems.net>
cc: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts 
In-reply-to: <plop8762wvspfb.fsf@aoskar.kilobug.org> 
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
Comments: In-reply-to Gael Le Mignot <gael@pilotsystems.net>
	message dated "Thu, 21 Oct 2010 14:25:44 +0200"
Date: Thu, 21 Oct 2010 10:55:48 -0400
Message-ID: <23677.1287672948@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Gael Le Mignot <gael@pilotsystems.net> writes:
> The problem is  when we index objects into the  full-text search part of
> the database (which a DELETE and  then an INSERT into a specific table),
> the INSERT  sometimes take a long time  (from 10s to 20s),  but the same
> insert (and many other similar ones) are fast (below 0.2s).

> This  slowness  comes  regularly,   about  every  200  objects  indexed,
> regardless  of the frequency  of the  inserts.

Hm.  You didn't say which PG version you're using, but if it's >= 8.4,
I think this may be caused by GIN's habit of queuing index insertions
until it's accumulated a reasonable-size batch:
http://www.postgresql.org/docs/9.0/static/gin-implementation.html#GIN-FAST-UPDATE

While you can turn that off, I think that doing so will reduce the
index's search efficiency over time.  It might be better to schedule
regular vacuums on the table so that the work is done by vacuum rather
than foreground queries.

			regards, tom lane