On 28 Apr 2011, at 10:07, Mark wrote:
> Alban thanks for your quick reply.
> It is true that I use for this only 2,5GB RAM on Intel Core i5 CPU 2.67GHz
> and resources I didn't changed from instalation of postgres:
> max_connections = 100
> shared_buffers = 32MB
> (other parameters are commented)
> , but that would not be the reason I think.
It probably is, the default Postgres settings are quite modest and GIN indexes are memory hungry.
I think you need to increase shared_buffers. With 2.5GB of memory (such a strange number) the docs suggest about 250MB.
See http://www.postgresql.org/docs/current/static/runtime-config-resource.html for details.
> I was maybe wrong to explain. As I said at first all was ok. That means that
> after creating the index one query took less time.
> For example query over:
> tsquery(liquid & water) - 38ms, 219 results
>
> but now it is like this:
> tsquery(liquid & water) - 2859ms, 219 results
Presumably the index did fit in your available shared buffers before your delete/insert actions, but now it doesn't
anymore(which possibly means there are still dead rows referenced by the index). I'm also not sure why you're getting a
bitmapindex scan instead of a normal index scan, maybe that's normal for GIN indexes (considering what I know about how
theywork internally, that seems plausible).
What version of Postgres is this?
If it's 9.0, you could try VACUUM FULL (before you increase shared_buffers) and see whether your performance is back
whereit was, which would indicate that dead rows are the cause. In older versions you could as well, but you'll want to
REINDEXafter that. That's not a solution though, only a test ;)
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4db987e912122982898556!