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.
