Search code examples
postgresqlperformanceindexingrds

Postgres GIN Index Performance Degregation

I have a Postgres GIN index on a table, t1, over a JSONB column a_col.

CREATE INDEX i_1 ON t1 USING gin (t1.a_col)

This takes a query runtime of ~2s down to ~50ms.

This table is somewhat active, taking ~1k new rows of data daily with no updates/deletes.

The problem I am seeing is the performance of this index dramatically falls off after a few hundred writes - query performance falls back to ~2s.

The issue is resolved after running a reindex operation.

REINDEX INDEX i_1

But this leaves the question what is driving the index to run so poorly?

Solution

  • While running

    select * from pg_stat_all_tables where relname = 't1'

    I was seeing that n_mod_since_analyze had >2k rows and last_autoanalyze was several days out.

    By dropping the value of autovacuum_analyze_scale_factor from 0.05 to 0.002, autoanalyze began to run move frequently and started to see more consistent performance.