From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gael Le Mignot <gael@pilotsystems.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Date: Thu, 21 Oct 2010 10:55:48 -0400
Message-ID: <23677.1287672948@sss.pgh.pa.us> (raw)
In-Reply-To: <plop8762wvspfb.fsf@aoskar.kilobug.org>
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gael@pilotsystems.net
Subject: Re: Periodically slow inserts
In-Reply-To: <23677.1287672948@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox