Re: Periodically slow inserts - Mailing list pgsql-performance

From Tom Lane
Subject Re: Periodically slow inserts
Date
Msg-id 23677.1287672948@sss.pgh.pa.us
Whole thread Raw
In response to Periodically slow inserts  (Gael Le Mignot <gael@pilotsystems.net>)
Responses Re: Periodically slow inserts  (Gael Le Mignot <gael@pilotsystems.net>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Brad Nicholson
Date:
Subject: Re: New wiki page on write reliability
Next
From: Gael Le Mignot
Date:
Subject: Re: Periodically slow inserts