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

Поиск
Список
Период
Сортировка
От rverghese
Тема Table not using tsvector gin index and performance much worse thanwhen it uses it.
Дата
Msg-id 1491432671098-5954485.post@n3.nabble.com
обсуждение исходный текст
Ответы Re: Table not using tsvector gin index and performance much worse than when it uses it.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.


В списке pgsql-performance по дате отправления:

Предыдущее
От: Dinesh Chandra 12108
Дата:
Сообщение: How to send content of log file in official mailid.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Table not using tsvector gin index and performance much worse than when it uses it.