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 have this query:

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount

I'm happy with it:

"Sort  (cost=3842.56..3847.12 rows=1826 width=123) (actual time=1.915..2.084 rows=1307 loops=1)"
"  Sort Key: displaycount"
"  Sort Method: quicksort  Memory: 206kB"
"  ->  Bitmap Heap Scan on location  (cost=34.40..3743.64 rows=1826 width=123) (actual time=0.788..1.208 rows=1307 loops=1)"
"        Recheck Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"        ->  Bitmap Index Scan on location_lower_idx  (cost=0.00..33.95 rows=1826 width=0) (actual time=0.760..0.760 rows=1307 loops=1)"
"              Index Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2.412 ms"

But when I add LIMIT, the execution takes more than 2 seconds:

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount 
limit 20

Explain:

"Limit  (cost=0.00..1167.59 rows=20 width=123) (actual time=2775.452..2775.643 rows=20 loops=1)"
"  ->  Index Scan using location_displaycount_index on location  (cost=0.00..106601.25 rows=1826 width=123) (actual time=2775.448..2775.637 rows=20 loops=1)"
"        Filter: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2775.693 ms"

I think that it's some issue with ORDER BY and LIMIT. How can I force PostgreSQL to use the index and do the ordering at the end?

Subquery doesn't help:

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
    order by displaycount
) t 
LIMIT 20;

or:

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
) t 
order by displaycount 
LIMIT 20;
share|improve this question

migrated from stackoverflow.com Jun 22 '12 at 13:54

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

2 Answers 2

up vote 8 down vote accepted

My guess is that this would fix your query:

SELECT * 
FROM   location 
WHERE     to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
ORDER  BY to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) DESC
         ,displaycount 
LIMIT  20;

I repeat the WHERE condition as first element of the ORDER BY clause - which is logically redundant, but should keep the query planner from assuming it would be better off processing rows according to the index location_displaycount_index - which turns out to be much more expensive.

The underlying problem is that the query planner obviously grossly misjudges the selectivity and / or cost of your WHERE condition. I can only speculate why that is.

Do you have autovacuum running - which should also take care of running ANALYZE on your tables? Thereby, are your table-statistics up-to-date? Any effect if you run:

ANALYZE location;

And try again?

It can also be that the selectivity of the @@ operator is being misjudged. I would imagine that it is very hard to estimate for logical reasons.


If my query should not solve the problem, and generally to verify the underlying theory do one of these two things:

  • temporarily delete the index location_displaycount_index

  • temporarily disable basic index scans by running:

    SET enable_indexscan = OFF;
    

The latter is less intrusive and only affects the current session. It leaves the methods bitmap heap scan and bitmap index scan open, which are used by the faster plan.
Then re-run the query.

BTW: If the theory is sound, your query (as you have it now) will be much faster with a less selective search term in the FTS condition - contrary to what you might expect. Try it.

share|improve this answer
1  
The query works. Turning the indexscan off works too. ANALYZE doesn't work. Thank you very much for comprehensive answer. –  ziri Jun 22 '12 at 20:34

When using a LIMIT postgresql adjust it's plan to be optimal for only retrieving the subset of row. Unfortunatly it somehow makes a wrong choice in your case. This could be because the statistics for the table are too old. Try updating the statistic by issuing VACUUM ANALYZE location;

Forcing the use of indexes is normally done by disallowing the use of sequential scans (set enable_seqscan=false). However in your case it isn't doing a sequential scan it just switches to a different index for the query with the LIMIT.

In case the analyze doesn't help could you tell which version of postgresql you are using? Also how many rows are there in the table?

share|improve this answer
    
Analyze didn't help. The table has about 36000 rows and I'm using postgresql 9.1. –  ziri Jun 22 '12 at 20:22

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.