Re: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

[rverghese]

Yup, I just found the per index option. Pretty cool. Will see what value is
optimal...

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

Re: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

[Tom Lane]

rverghese  writes:
> Will play around with those settings as well. Maybe start with default which
> is 50 I believe.

If you're on 9.5, auto-analyze does not result in a pending list flush,
so it's irrelevant to fixing your problem.  (Assuming I've identified
the problem correctly.)  But you do have gin_pending_list_limit, so see
what that does for you.  Note you can set it either globally or per-index.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

[rverghese]

Ok, appreciate the feedback. 
Will play around with those settings as well. Maybe start with default which
is 50 I believe.
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-tp5954485p5954509.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

Re: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

[Sfiligoi, Igor]

>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-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@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 = 2  # 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<mailto:pgsql-performance@postgresql.org>)

To make changes to your subscription:

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