text search index help
От | Campbell, Lance |
---|---|
Тема | text search index help |
Дата | |
Msg-id | B75CD08C73BD3543B97E4EF3964B7D701FC75B15@CITESMBX1.ad.uillinois.edu обсуждение исходный текст |
Список | pgsql-sql |
PostgreSQL 9.3 Use case: I have a blog tool that will allow users to create any number of blogs. Each blog could contain N number of posts. When a user wishes to search for a post they only want to search within a particular blog. I created the below view with the idea I would search against the field "document" for content within any particular fk_blog_id. The challenge is how to build an appropriate index. Ideally the index would be fk_blog_id and then document. Any help you could give me would be greatly appreciated. CREATE MATERIALIZED VIEW blog.post_search AS SELECT post.fk_blog_id, post.id as fk_post_id, setweight(to_tsvector('simple', post.title), 'A') || setweight(to_tsvector('simple', post.summary || post.article), 'B') || setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'C') as document FROM blog.post JOIN blog.tag ON tag.fk_post_id = post.id WHERE post.is_deleted=false AND post.is_published=true AND post.is_post=true AND ( (post.article_type='article') OR (post.is_summary_enabled=true) ) GROUP BY post.fk_blog_id,post.id; Thanks, Lance Campbell Software Architect Web Services at Public Affairs 217-333-0382
В списке pgsql-sql по дате отправления: