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.