Re: Postgres 9.1: Adding rows to table causing too much latency in other queries

On 2011-12-19 18:08, Marti Raudsepp wrote:
>> The query uses the gin index and the tsearch ranking function
>> ts_rank_cd.
> Another thought -- have you read about the GIN "fast updates" feature?
> This existed in 9.0 too. Instead of updating the index directly, GIN
> appends all changes to a sequential list, which needs to be scanned in
> whole for read queries. The periodic autovacuum process has to merge
> these values back into the index.
>
> Maybe the solution is to tune autovacuum to run more often on the table.
>
> http://www.postgresql.org/docs/9.1/static/gin-implementation.html
>
I have to say that I consistently have to turn "fastupdate" off for
our heavily updated gin-indexes. The overall performance gain
may be measurable, but its not intolerable without. The spikes seen
from the applications, when cleanup happens. Either in the foreground
or in the background are not tolerable. (multiple seconds).

I may just not have experienced suffienctly with the various sizes of
work_mem,
but I would indeed love to have a "connection only fastupdate", so
within a single
transaction it could use the fastupdate technique, but not stuffing
index-updates
onto unreleated queries by random.

Jesper
--
Jesper