gin performance issue.

Postgres Version 9.3.10 (Linux)

 

Hello,

this is a large daily table that only get bulk inserts (200-400 /days) with no update.

After rebuilding the whole table, the Bitmap Index Scan on r_20160204_ix_toprid falls under 1 second (from 800)

 

Fastupdate is using the default, but autovacuum is disabled on that table which contains 30 Mio rows.

Another specificity is that the cardinality of the indexed column is very high. The average count per distinct values is only 2.7

 

I'm not sure what the problem is. Does the missing vacuum affect the gin index sanity further than not cleaning the pending list?

As I understand it, this list will be merged into the index automatically when it get full, independently from the vaccum setting.

 

Can it be an index bloating issue ?

 

and last but not least, can I reduce the problem by configuration ?

 

regards,

 

Marc Mamin