Re: -EXT-Re: Table not using tsvector gin index andperformance much worse than when it uses it.

From my experience, you want to really tighten the autovacuum_analyze parameters.

I recommend our users to use:

autovacuum_analyze_threshold = 1

autovacuum_analyze_scale_factor = 0.0

Analyze is quite cheap, and the speed difference between an optimal and a suboptimal plans are usually pretty big.

My 2c,

  Igor

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of rverghese
Sent: Wednesday, April 05, 2017 4:54 PM
To: pgsql-performance@postgresql.org
Subject: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

Thanks for the response!

* We are on version 9.5.6

* Less than 10% of the table was updated today (between the time of the last reindex to when performance deteriorated)

* autovacuum is on. I don't see an autoanalyze property in config but these are the settings for analyze

/autovacuum_analyze_threshold = 3000     # min number of row updates before

analyze

#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before

vacuum

#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum

                                        # (change requires restart)/

* And this #gin_pending_list_limit = 4MB

* gin_clean_pending_list() is not available.

Will play with gin_pending_list_limit and see what that does.

Thanks!

RV

--

View this message in context: http://www.postgresql-archive.org/Table-not-using-tsvector-gin-index-and-performance-much-worse-than-when-it-uses-it-tp5954485p5954503.html

Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

--

Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-performance