Re: How to speed up pg_trgm / gin index scan

On 22/06/15 20:32, Jaime Casanova wrote:

> What version of postgres is this? GIN indexes improved a lot in 9.4,
> they use less than half the space and have doubled the speed (on
> average).
>
> Now, whatever version you have; a GIN index has two data structures,
> the main one in which the index entries are stored as key-value pairs
> (please someone correct my description of the situation) and a pending
> list, which is a temporary unsorted list of pending entries in which
> all the newly inserted tuples arrive until a VACUUM (or until the
> pending list grows upto work_mem) moves that list into the main
> structure.
>
> That happens to avoid the penalty of inserting new rows in the main
> structure which could be expensive.
> But while the pending list grows the speed of the index decreases. And
> because you have work_mem in 16Gb your pending list is possibly
> growing without control.
>
> if you have 9.3 or superior you can know how big is that pending list
> installing pgstattuple.
>
> CREATE EXTENSION pgstattuple;
> SELECT * FROM pgstatginindex('tridx_logs_01_msg');
>
> NOTE: remember that pending_pages is expressed in 8kb-pages
>
> if that is the problem or if you are in <= 9.2 then try VACUUM the table
>

Thanks, these are some interesting details I wasn't aware of. I think
the pending list shouldn't be an issue since I restored this table into
a new installation from backup and it receives no new data at all.

But it is in 9.1.15 so I try to give 9.4 a shot, as apparently I'm
missing out on a lot of stuff.

Christian