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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
