Slow GIN indexes after bulk insert

From: Chris Spencer <chrisspen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow GIN indexes after bulk insert
Date: 2016-03-21 17:55:12
Message-ID: CANe40gJazydtipngFgZdThjDY1CNqQNqeMdZEG_tWAFXQo1HfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

If I drop and recreate the indexes, then performance returns to normal, but
this causes a couple hours of downtime, even if I recreate indexes
concurrently.

Why are these GIN indexes becoming unusable after a large number of
inserts? Is this a known limitation or is there anything I can do to fix
this?

Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-03-21 18:03:17 Re: postgresql timezone and OS localtime correspondence
Previous Message Tom Lane 2016-03-21 17:42:20 Re: [GENERAL] Request - repeat value of \pset title during \watch interations