On 08/02/13 01:52, Stefan Keller wrote:
Hi,
I have problems with the performance of FTS in a query like this:
SELECT * FROM FullTextSearch WHERE content_tsv_gin @@
plainto_tsquery('english', 'good');
It's slow (> 30 sec.) for some GB (27886 html files, originally 73 MB zipped).
The planner
Hello
you can try to wrap searching to immutable function and use following trick
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Using_IMMUTABLE_functions_as_hints_for_the_optimizer
Regards
Pavel Stehule
2013/2/8 Stefan Keller :
> Hi,
>
> I have problems with the performance of FTS in a query l
Hi,
I have problems with the performance of FTS in a query like this:
SELECT * FROM FullTextSearch WHERE content_tsv_gin @@
plainto_tsquery('english', 'good');
It's slow (> 30 sec.) for some GB (27886 html files, originally 73 MB zipped).
The planner obviously always chooses table scan: http:/
Just as an update from my angle on the THP side... I put together a
systemtap script last night and so far it's confirming my theory (at least
in our environment). I want to go through some more data and make some
changes on our test box to see if we can make it go away before declaring
success -
I appreciate all the responses on this thread, even though some are
conflicting :). We are going to try these one at a time, but we'll likely
need a day or so inbetween each to see what impact (if any), so it will
take time. But I will post back here our findings.
We'll start with dirty_background
I've benchmarked shared_buffers with high and low settings, in a server
dedicated to postgres with 48GB my settings are:
shared_buffers = 37GB
effective_cache_size = 38GB
Having a small number and depending on OS caching is unpredictable, if the
server is dedicated to postgres you want make su
As others suggested having shared_buffers = 48GB is to large. You should
never need to go above 8GB. I have a similar server and mine has
shared_buffers = 8GB
checkpoint_completion_target = 0.9
This looks like a problem of dirty memory being flushed to the disk. You
should set your monitoring to
