I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index ON bodies USING gin (to_tsvector('english'::regconfig, body));
and to query it I use
SELECT * FROM bodies WHERE to_tsvector('english', body) @@ plainto_tsquery('english', '" + searchterm + "')"
The results are good for almost all database sizes but there is one exception. It takes up to 10x longer for the biggest database of 250 000 records, but only for results with a high selectivity.
Any ideas how this could happen?
EDIT: My PG version is 9.2.
Here is the result for EXPLAIN ANALYZE for a fast result: (17k results in under 100ms)
And this one is much slower, despite only a small rise in the number of results (28k results in over 50 seconds)