如何在没有子句的情况下改进此查询

Question

有两张桌子：

1. 用户
2. 文档

users

CREATE TABLE IF NOT EXISTS users (
    id         SERIAL PRIMARY KEY,
    username   TEXT NOT NULL UNIQUE
)

documents

CREATE TABLE IF NOT EXISTS documents (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL REFERENCES users,
    name       TEXT NOT NULL,
    value      INT NOT NULL 
)

我想通过文档users和value过滤name。通常，在2-5文档name和value之间进行过滤.每个用户有6到10个文档。

我有一个庞大的数据库，希望改进这个查询。我认为不使用HAVING子句可以获得更快的查询。任何帮助都非常感谢。我使用PostgreSQL 13。

我使用的查询：

SELECT
    users.username,
    jsonb_agg(jsonb_strip_nulls(jsonb_build_object('name', documents.name, 'value', documents.value))) AS docs
FROM
    users
JOIN
    documents
ON
    users.id = documents.user_id
GROUP BY
    users.username
HAVING
    jsonb_agg(jsonb_build_object('name', documents.name, 'value', documents.value)) @? '$[*] ? (@.name == "doc1") ? (@.value == "2")'

Answer 1

对于大表，在最后过滤少数限定行之前，加入和聚合所有行是非常昂贵的。

首先过滤符合条件的文档，然后获取同一用户的所有文档，聚合，最后加入到用户，应该会更快：

SELECT u.username, d.docs
FROM  (
   SELECT user_id, jsonb_agg(jsonb_build_object('name', d.name, 'value', d.value)) AS docs
   FROM   documents d1
   JOIN   documents d USING (user_id)
   WHERE  d1.name = 'doc1'
   AND    d1.value = 2
   -- AND    d.name  IS NOT NULL  -- strip NULLs early 
   -- AND    d.value IS NOT NULL  -- if not defined NOT NULL anyway
   GROUP  BY 1
   ) d
JOIN   users u ON u.id = d.user_id;

在此过程中，我删除了jsonb_strip_nulls()，因为所有处理过的列都定义为NULL。也更便宜。

可能简化为只有jsonb_build_object(d.name, d.value)。

作为第一步，索引 on documents(name, value)将对有很大的帮助。甚至可以通过documents(name, value, user_id)获得只索引扫描 (取决于)。

假设documents(user_id)上也有索引，应该是安全的。有助于下一步。同样，documents(user_id, name, value)只用于索引扫描.

最后，在users(id)上建立一个索引。应该是既定的。同样，users(id, username)只用于索引扫描.

如果(name, value)不是每个用户的UNIQUE (似乎是这样)，那么使用EXISTS来避免重复：

SELECT u.username, d.docs
FROM  (
   SELECT user_id, jsonb_agg(jsonb_build_object('name', d.name, 'value', d.value)) AS docs
   FROM   documents d
   WHERE  EXISTS (
      SELECT FROM documents d1
      WHERE  d1.user_id = d.user_id
      AND    d1.name = 'doc1'
      AND    d1.value = 2
      )
   GROUP  BY 1
   ) d
JOIN   users u ON u.id = d.user_id;

查询计划相似，可以使用相同的索引。

相关信息：

• 使用左联接查询不返回计数为0的行
• 复合索引对第一个字段的查询也有好处吗？