pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTable not using tsvector gin index and performance much worse than when it uses it.
3+ messages / 2 participants
[nested] [flat]
* Table not using tsvector gin index and performance much worse than when it uses it.
@ 2017-04-05 22:51 rverghese <riyav@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: rverghese @ 2017-04-05 22:51 UTC (permalink / raw)
To: 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...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table not using tsvector gin index and performance much worse than when it uses it.
@ 2017-04-05 23:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: rverghese <riyav@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2017-04-05 23:19 UTC (permalink / raw)
To: rverghese <riyav@hotmail.com>; +Cc: pgsql-performance
rverghese <riyav@hotmail.com> writes:
> 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.
You haven't mentioned what PG version this is, nor specified how many
updates is "hardly any", so you shouldn't expect any very precise answers.
But I'm suspicious that the problem is bloat of the index's pending list;
the planner's cost estimate is (correctly) pretty sensitive to the length
of that list. If so, you need to arrange for the pending list to get
flushed into the main index structure more often. Depending on your PG
version, that can be done by
* vacuum
* auto-analyze (but I bet your version doesn't, or you would not be
complaining)
* gin_clean_pending_list() (but you probably ain't got that either)
Or you could reduce gin_pending_list_limit to cause insert-time flushes to
happen more often, or in the extremum even disable fastupdate for that
index. Those options would slow down updates to make search performance
more stable, so they're not panaceas.
See
https://www.postgresql.org/docs/current/static/gin-implementation.html#GIN-FAST-UPDATE
for your version, also the "GIN Tips" on the next page.
Personally I'd try tweaking gin_pending_list_limit first, if you have
a version that has that ... but YMMV.
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table not using tsvector gin index and performance much worse than when it uses it.
@ 2017-04-05 23:54 rverghese <riyav@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: rverghese @ 2017-04-05 23:54 UTC (permalink / raw)
To: pgsql-performance
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...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-04-05 23:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-05 22:51 Table not using tsvector gin index and performance much worse than when it uses it. rverghese <riyav@hotmail.com>
2017-04-05 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-04-05 23:54 ` rverghese <riyav@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox