Problems with FTS

From: Rauan Maemirov <rauan(at)maemirov(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Problems with FTS
Date: 2010-12-15 13:56:33
Message-ID: AANLkTimp6toH6FafjkOwcQfqo++8PFFiLgo75=5aAP-i@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi, all. I'm trying to query table:

EXPLAIN SELECT "v"."id", "v"."title" FROM "video" AS "v"
WHERE (v.active) (v.fts @@
'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery and
v.id <> 500563 )
ORDER BY COALESCE(ts_rank_cd( '{0.1, 0.2, 0.7, 1.0}', v.fts,
'dexter:A|season:A|seri:A|декстер:A|качество:A|сезон:A|серия:A'::tsquery),
1) DESC,
v.views DESC
LIMIT 6

Here's the query that gets all related items, where fts is tsvector field
with index on it (CREATE INDEX idx_video_fts ON video USING gin (fts);)
earlier i tried gist, but results are the same.

And here's what i got:

"Limit (cost=98169.89..98169.90 rows=6 width=284)"
" -> Sort (cost=98169.89..98383.16 rows=85311 width=284)"
" Sort Key: (COALESCE(ts_rank_cd('{0.1,0.2,0.7,1}'::real[], fts, '( (
( ( ( ''dexter'':A | ''season'':A ) | ''seri'':A ) | ''декстер'':A ) |
''качество'':A ) | ''сезон'':A ) | ''серия'':A'::tsquery), 1::real)), views"
" -> Seq Scan on video v (cost=0.00..96640.70 rows=85311
width=284)"
" Filter: (active AND (fts @@ '( ( ( ( ( ''dexter'':A |
''season'':A ) | ''seri'':A ) | ''декстер'':A ) | ''качество'':A ) |
''сезон'':A ) | ''серия'':A'::tsquery) AND (id <> 500563))"

As you can see the query doesn't use index. If I drop "or" sentences for the
query, it will, but I do need them. I'm using PostgreSQL 9.0.
What should I do? The query is really too slow.

Browse pgsql-performance by date

  From Date Subject
Next Message Werner Scholtes 2010-12-15 14:51:55 performance libpq vs JDBC
Previous Message Marti Raudsepp 2010-12-15 08:52:03 Re: only one index is using, why?