logo标签列表

PostgreSQL搜索/索引（GIN索引？）跨越两个列/数组

sqlpostgresqldatabase-indexes
3
假设我们有一个PostgreSQL表contacts，每个记录都有一堆带标签的电子邮件地址（标签和电子邮件对），其中之一是“主要”。

存储方式如下：

  • id 主键
  • email 文本
  • email_label 文本
  • metadata jsonb
    • emails 数组
    • email 文本
    • label 文本

例如，一条记录可能看起来像这样：

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索引和这个问题提到的jsonb_path_ops，但我不确定如何将所有部分拼凑在一起。
1个回答
2
创建以下两个索引：
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)
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接