Re: GIN index not used

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 bitmap index scan instead of a normal index scan, maybe that's normal for GIN indexes (considering what I know about how they work 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 where it was, which would indicate that dead rows are the cause. In older versions you could as well, but you'll want to REINDEX after 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!