Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

http://explain.depesz.com/s/i1F

And this one is much slower, despite only a small rise in the number of results (28k results in over 50 seconds)

http://explain.depesz.com/s/WwpX

share|improve this question
5  
Pg version? EXPLAIN (BUFFERS, ANALYZE) ? –  Craig Ringer Apr 1 '13 at 7:07
    
I added the information. I guess the Index Recheck is responsible for the longer search time? –  Seppl Apr 3 '13 at 2:26
    
Really no one who has an idea what to do? If this is not information just let me know, I can provide more if necessary –  Seppl Apr 4 '13 at 4:57
    
I didn't see the 1st comment; consider @ replying if you want someone to be notified. Those query plans are almost totally unreadable, the formatting is gone. You need to include them as code, not quotes, or (best) paste them to explain.depesz.com and add the links to your post. –  Craig Ringer Apr 4 '13 at 5:01
    
@CraigRinger Thanks for the tip, this is much better readable now with the explain.depesz links –  Seppl Apr 6 '13 at 2:06
show 1 more comment

migrated from stackoverflow.com May 1 '13 at 15:47

This question came from our site for professional and enthusiast programmers.

1 Answer

As I understand your question you are asking why highly selective index scans might be much slower after a certain number of records are returned or after the table reaches a certain size. As it turns out your query plans provide most of the information needed. Understanding of course is the first step in trying to figure out how to solve the problem. It looks to me like your slow query is hitting a much more heavily used db than the fast query.

As I look at your query plans, I see two immediate pieces of bad news.

The first piece of bad news is in the buffer usage. Your rows returned are less than double, but you hit about six times the number of buffer pages. This is bad news because it means that PostgreSQL, following the index scan, is scanning through at least six times more information to find the rows to retrieve. Significantly worse, you shift from the rarely used db of mostly read buffers (fast, but few db-specific services) to shared buffers which require more overhead.

The second piece of bad news is that the recheck condition in the slow query weeds out about three times as many records as are returned. This tells me you need to vacuum and/or reindex this table.

I would recommend vacuum analyze and reindex first, followed if necessary by slightly lowering the shared buffer settings and see if this improves performance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.