Date: Wed, 5 Apr 2017 15:51:11 -0700 (MST)
From: rverghese <riyav@hotmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1491432671098-5954485.post@n3.nabble.com>
Subject: Table not using tsvector gin index and performance much worse than
 when it uses it.
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,
I have a table with 22k rows - not large at all. I have a couple of indices
on it as well as a gin index on  a tsvector column. If I reindex the table
and run a query it takes ~20ms to execute using the tsvector-gin index. By
the end of the day, the planner decides not to use the gin index and uses
the other indices on the table and the query takes ~80ms. If I reindex, the
pattern repeats-it uses the gin index for a while for superior performance
and then drops back to using the alternate ones. 
The ibloat on the index shows as 0.4 and wastedibytes is 0.  Less than 2K
rows have been updated of the 22K since the last reindex but the performance
has dropped since it is no longer using the gin index by mid-day. 
Any thoughts on why it chooses to use alternate indices with hardly any
updates? And is there a way to force it to use the gin index without having
to reindex it twice a day.
Thanks!


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