Table not using tsvector gin index and performance much worse than when it uses it.

From: rverghese <riyav(at)hotmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Table not using tsvector gin index and performance much worse than when it uses it.
Date: 2017-04-05 22:51:11
Message-ID: 1491432671098-5954485.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2017-04-05 23:19:57 Re: Table not using tsvector gin index and performance much worse than when it uses it.
Previous Message Dinesh Chandra 12108 2017-04-05 16:53:05 How to send content of log file in official mailid.