sql - 跨两列/数组的 PostgreSQL 搜索/索引(GIN 索引？)

Question

假设我们有一个 PostgreSQL 表contacts，每条记录都有一堆带标签的电子邮件地址(标签和电子邮件对)——其中一个是“主要”。

存储方式如下:

• id 主键
• 电子邮件 文本
• email_label 文本
• 元数据 jsonb
  • emails 数组
  • 电子邮件 文本
  • 标签文本

例如，一条记录可能类似于:

id: 1
email: 'a@a.com'
email_label: 'a'
metadata: {
            "emails": [
                        {
                          "email": "b@b.com",
                          "label": "b"
                        },
                        {
                          "email": "c@c.com",
                          "label": "c"
                        }
                      ]
          }

鉴于此存储模式，我们希望能够通过其任何电子邮件地址找到记录。

简单的查询看起来像:

SELECT id
FROM contacts
WHERE
  email = 'my@email.com' OR
  metadata -> 'emails' @> '[{"email": "my@email.com"}]'

有没有什么方法可以创建一个索引来显着加快这个操作？它需要自动更新以响应记录的更改，理想情况下索引跨文本列和嵌套JSONB 列。

此处的特定用例将能够高效快速地通过电子邮件地址进行查找，而无需彻底检查此结构或创建新的关系表。

我认为解决方案涉及使用 GIN 索引和 this question mentions jsonb_path_ops但我不确定如何将所有部分整合在一起。

Answer 1

创建以下两个索引:

CREATE INDEX contacts_email_idx
   ON contacts (email);

CREATE INDEX contacts_metadata_emails_idx
   ON contacts USING gin ((metadata -> 'emails') jsonb_path_ops);

那么查询会很快，因为索引完全符合这两个条件，并且可以使用位图索引扫描进行组合。

EXPLAIN (COSTS off)
SELECT id
FROM contacts
WHERE email = 'my@email.com'
   OR metadata -> 'emails' @> '[{"email": "my@email.com"}]';

                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on contacts
   Recheck Cond: ((email = 'my@email.com'::text) OR ((metadata -> 'emails'::text) @> '[{"email": "my@email.com"}]'::jsonb))
   ->  BitmapOr
         ->  Bitmap Index Scan on contacts_email_idx
               Index Cond: (email = 'my@email.com'::text)
         ->  Bitmap Index Scan on contacts_metadata_emails_idx
               Index Cond: ((metadata -> 'emails'::text) @> '[{"email": "my@email.com"}]'::jsonb)
(7 rows)