sql - 多个表的 View 可以用于全文搜索吗？

Question

很抱歉问这样一个菜鸟问题，但是 postgres documentation意见很少，我很难找到一个好的答案。

我正在尝试在 Postgres 上为三个表实现全文搜索。具体来说，用户的搜索查询将返回匹配的 1) 其他用户名，2) 消息，3) 主题。

我担心为此使用 View 可能无法很好地扩展，因为它将三个表合并为一个。这是一个合理的担忧吗？如果不是，我还能如何解决这个问题？

Answer 1

您所要求的都可以完成。要有一个实际的例子(只有两个表)，你可以:

CREATE TABLE users
(
    user_id SERIAL PRIMARY KEY,
    username text
) ;

-- Index to find usernames
CREATE INDEX idx_users_username_full_text 
    ON users 
    USING GIN (to_tsvector('english', username)) ;        

CREATE TABLE topics
(
    topic_id SERIAL PRIMARY KEY,
    topic text
) ;

-- Index to find topics
CREATE INDEX idx_topics_topic_full_text 
    ON topics 
    USING GIN (to_tsvector('english', topic)) ;

请参阅 PostgreSQL 文档。在 Controlling Text Search to_tsvector 的解释。

...填充表格

INSERT INTO users
   (username)
VALUES
   ('Alice Cooper'),
   ('Boo Geldorf'),
   ('Carol Burnet'),
   ('Daniel Dafoe') ;

INSERT INTO topics
   (topic)
VALUES
   ('Full text search'),
   ('Fear of void'),
   ('Alice in Wonderland essays') ;

...创建一个合并两个表中值的 View

CREATE VIEW search_items AS
SELECT 
    text 'users' AS origin_table, user_id AS id, to_tsvector('english', username) AS searchable_element
FROM
    users
UNION ALL
SELECT 
    text 'topics' AS origin_table, topic_id AS id, to_tsvector('english', topic) AS searchable_element 
FROM
    topics ;

我们搜索那个 View :

SELECT 
    *
FROM
    search_items
WHERE
    plainto_tsquery('english', 'alice') @@ searchable_element

... 并获得以下响应(您应该主要忽略 searchable_element)。您最感兴趣的是 origin_table 和 id。

origin_table | id | searchable_element               :----------- | -: | :--------------------------------users        |  1 | 'alic':1 'cooper':2              topics       |  3 | 'alic':1 'essay':4 'wonderland':3

See Parsing Queries for an explanation of plainto_tsquery function, and also @@ operator.

---

To make sure indexes are used:

EXPLAIN ANALYZE
SELECT 
    *
FROM
    search_items
WHERE
    plainto_tsquery('english', 'alice') @@ searchable_element

| QUERY PLAN                                                                                                                                 || :----------------------------------------------------------------------------------------------------------------------------------------- || Append  (cost=12.05..49.04 rows=12 width=68) (actual time=0.017..0.031 rows=2 loops=1)                                                     ||   ->  Bitmap Heap Scan on users  (cost=12.05..24.52 rows=6 width=68) (actual time=0.017..0.018 rows=1 loops=1)                             ||         Recheck Cond: ('''alic'''::tsquery @@ to_tsvector('english'::regconfig, username))                                                 ||         Heap Blocks: exact=1                                                                                                               ||         ->  Bitmap Index Scan on idx_users_username_full_text  (cost=0.00..12.05 rows=6 width=0) (actual time=0.005..0.005 rows=1 loops=1) ||               Index Cond: ('''alic'''::tsquery @@ to_tsvector('english'::regconfig, username))                                             ||   ->  Bitmap Heap Scan on topics  (cost=12.05..24.52 rows=6 width=68) (actual time=0.012..0.012 rows=1 loops=1)                            ||         Recheck Cond: ('''alic'''::tsquery @@ to_tsvector('english'::regconfig, topic))                                                    ||         Heap Blocks: exact=1                                                                                                               ||         ->  Bitmap Index Scan on idx_topics_topic_full_text  (cost=0.00..12.05 rows=6 width=0) (actual time=0.002..0.002 rows=1 loops=1)   ||               Index Cond: ('''alic'''::tsquery @@ to_tsvector('english'::regconfig, topic))                                                || Planning time: 0.098 ms                                                                                                                    || Execution time: 0.055 ms                                                                                                                   |

确实使用了索引(参见idx_topics_topic_full_text 上的位图索引扫描 和idx_users_username_full_text 上的位图索引扫描)。

您可以在 dbfiddle here 查看所有内容

---

注意:'english' 是 text search configuration选择索引和查询。为您的情况选择合适的。如果现有的不能满足您的需求，您可以创建自己的。