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
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 |