Re: gin performance issue.

От: Marc Mamin
Тема: Re: gin performance issue.
Дата: ,
Msg-id: B6F6FD62F2624C4C9916AC0175D56D88420E12DB@jenmbs01.ad.intershop.net
(см: обсуждение, исходный текст)
Ответ на: Re: gin performance issue.  (Tom Lane)
Ответы: Re: gin performance issue.  (Jeff Janes)
Список: pgsql-performance

Скрыть дерево обсуждения

gin performance issue.  (Marc Mamin, )
 Re: gin performance issue.  (Tom Lane, )
  Re: gin performance issue.  (Marc Mamin, )
   Re: gin performance issue.  (Jeff Janes, )


> -----Original Message-----
> From: Tom Lane [mailto:]
> Sent: Freitag, 5. Februar 2016 16:07


> > http://explain.depesz.com/s/wKv7
> > Postgres Version 9.3.10 (Linux)
> >
> > Hello,
> > this is a large daily table that only get bulk inserts (200-400 /days) with no update.
> > After rebuilding the whole table, the Bitmap Index Scan on
> > r_20160204_ix_toprid falls under 1 second (from 800)
> >
> > Fastupdate is using the default, but autovacuum is disabled on that
> > table which contains 30 Mio rows.


> Pre-9.5, it's a pretty bad idea to disable autovacuum on a GIN index,
> because then the "pending list" only gets flushed when it exceeds
> work_mem.  (Obviously, using a large work_mem setting makes this
> worse.)
>
>             regards, tom lane


Hello,
knowing what the problem is don't really help here:

- auto vacuum will not run as these are insert only tables
- according to this post, auto analyze would also do the job:
  http://postgresql.nabble.com/Performance-problem-with-gin-index-td5867870.html
  It seems that this information is missing in the doc

  but it sadly neither triggers in our case as we have manual analyzes called during the dataprocesssing just following
theimports. 
  Manual vacuum is just too expensive here.

  Hence disabling fast update seems to be our only option.

  I hope this problem will help push up the 9.5 upgrade on our todo list :)

  Ideally, we would then like to flush the pending list inconditionally after the imports.
  I guess we could achieve something approaching while modifying the analyze scale factor  and gin_pending_list_limit
  before/after the (bulk) imports, but having the possibility to flush it per SQL would be better.
  Is this a reasonable feature wish?

  And a last question: how does the index update work with bulk (COPY) inserts:
  without pending list: is it like a per row trigger or will the index be cared of afterwards ?
  with small pending lists : is there a concurrency problem, or can both tasks cleanly work in parallel ?

  best regards,

  Marc mamin





В списке pgsql-performance по дате сообщения:

От: Merlin Moncure
Дата:
Сообщение: Re: bad COPY performance with NOTIFY in a trigger
От: Jeff Janes
Дата:
Сообщение: Re: Bitmap and-ing between btree and gin?