Re: Slow GIN indexes after bulk insert

On Mon, Mar 21, 2016 at 10:55 AM, Chris Spencer <chrisspen(at)gmail(dot)com> wrote:
> I have 5 GIN indexes on a PG 9.3 table containing about 50 million records.
> Each index covers a "group" of common records like billing address, shipping
> address, contact names, etc.
>
> When first created, the indexes works miracles in speeding up the full text
> search of these fields. However, I'm running into some serious maintenance
> headaches.
>
> After I insert a few thousand new records, the indexes seem to have no
> effect. A query that might normally take 1 second now takes 5 minutes.

I'd try turning fastupdate off on the indexes. It will probably slow
down your bulk inserts, (perhaps by a lot, but you won't know until
you try). But if you can handle that, it will make your slow select
problems go away like magic.

Cheers,

Jeff