From: | "Sfiligoi, Igor" <Igor(dot)Sfiligoi(at)ga(dot)com> |
---|---|
To: | rverghese <riyav(at)hotmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: -EXT-Re: Table not using tsvector gin index and performance much worse than when it uses it. |
Date: | 2017-04-06 00:09:28 |
Message-ID: | fb871f19d7894a0cb5e60c7dfac17534@TOREXCPWP03.ga.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of rverghese
Sent: Wednesday, April 05, 2017 4:54 PM
To: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org<mailto:pgsql-performance(at)postgresql(dot)org>)
To make changes to your subscription:
From | Date | Subject | |
---|---|---|---|
Next Message | rverghese | 2017-04-06 00:26:52 | Re: -EXT-Re: Table not using tsvector gin index and performance much worse than when it uses it. |
Previous Message | rverghese | 2017-04-05 23:54:00 | Re: Table not using tsvector gin index and performance much worse than when it uses it. |