Re: Performance problem with gin index

On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <
bertrand(dot)paquet(at)doctolib(dot)fr> wrote:

> Hi,
>
> We have got big slow down on our production plateform (PG 9.4.4).
>

What is it slow compared to? Did your version change, or your
workload/usage change?

>
> After analyzing wals with pg_xlogdump, we see lot of writing in Gin
> Indexes.
>
> We suspect slow down are related to the write of pending update on the
> index.
>
> So, is there any method to see
> - what is the current config of gin_pending_list_limit on a given index ?
>

gin_pending_list_limit will be introduced in 9.5. In 9.4 and before, there
is no such parameter. Instead, the limit is tied to the setting of
work_mem in those versions.

> - the current size of pending list on a given index ?
>

You can use this from the pgstattuple contrib module:

SELECT * FROM pgstatginindex('test_gin_index');

Your best bet may be to turn off fastupdate. It will slow down most
inserts/updates, but you will not have the huge latency spikes you get with
fastupdate turned on.

Also, you might (or might not) have a higher overall throughput with
fastupdate turned off, depending on a lot of things like the size of the
index, the size of ram and shared_buffers, the number of spindles in your
RAID, the amount of parallelization in your insert/update activity, and the
distribution of "keys" among the data you are inserting/updating.

Cheers,

Jeff