Re: gin performance issue.

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> 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 the imports.
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