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.
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? |