在一对多关系中，通过“多”侧的值过滤“一”侧的有效方法？

从逻辑上讲，和条件的混合是没有意义的。看：LEFT JOINWHERE

• 更详细地解释 JOIN 与 LEFT JOIN 和 WHERE 条件性能建议

基本重写：

SELECT *
FROM   users u
JOIN   users_attributes ua1 ON u.id = ua1.user_id
JOIN   users_attributes ua2 ON u.id = ua2.user_id
WHERE  ua1.attribute_name = 'dog_name'
AND    ua1.attribute_value = 'Spot'
AND    ua2.attribute_name = 'cat_name'
AND    ua2.attribute_value = 'Mittens';

基本上，这是一个关系分裂的情况。

有很多方法可以做到这一点。最佳查询样式取决于您的基数、典型筛选器以及您正在优化的内容。这是一整套武器库：

• 如何在具有多个直通关系中筛选 SQL 结果

我给出的查询是最快的选项之一。当然，您需要匹配的索引。通过适当的规范化，一切都会更有效率，其中属性名称移动到一个单独的表中，并且是一个指向那里的整数 FK。（两列）的索引将是理想的。看：attributeattribute_iduser_attribute(attribute_id, user_id)integer

• 复合索引是否也适用于第一个字段的查询？

查询会将属性名称解析为整数 ID（在查询中显式或隐式），并继续执行这些 ID。

这种类型的查询是典型的“craigslist 查询”，它基于属性（制造商、型号等）进行搜索......例如，它也可以应用于约会网站。

让我们构建一些测试数据。

CREATE UNLOGGED TABLE users( user_id INTEGER NOT NULL );
INSERT INTO users SELECT generate_series( 1, 1000000 );
ALTER TABLE users ADD PRIMARY KEY( user_id );

CREATE UNLOGGED TABLE users_attrs( 
 user_id INTEGER NOT NULL, 
 attr_id INTEGER NOT NULL  );
INSERT INTO users_attrs SELECT user_id, aid FROM (
    SELECT user_id, aid, 0.5/aid > random() x
    FROM generate_series(1,20) aid CROSS JOIN users ) foo
    WHERE x;
ALTER TABLE users_attrs ADD PRIMARY KEY (user_id,attr_id);
CREATE INDEX users_attrs_au ON users_attrs( attr_id, user_id );
SELECT attr_id,count(*) FROM users_attrs GROUP BY 1 ORDER BY 2;

 attr_id | count
---------+--------
      20 |  25104
      19 |  26570
      18 |  27638
      17 |  29574
      16 |  30982
      15 |  33490
      14 |  35574
      13 |  38473
      12 |  41816
      11 |  45373
      10 |  49641
       9 |  55793
       8 |  62471
       7 |  71386
       6 |  83123
       5 |  99592
       4 | 124920
       3 | 166107
       2 | 250662
       1 | 500446

我没有将属性名称放在users_attrs中，因为它应该放在单独的表中。

为简单起见，我没有使用属性值。无论我们在 （attribute_id，user_id） 还是 （attribute_id，attribute_value，user_id） 上使用索引，为了进行性能衡量，结果都是相同的。在搜索时，重要的是概率分布，换句话说，搜索条件的选择性。

例如，假设您正在约会网站上寻找“您附近的 25-30 岁女性”。首先基于“性别”进行搜索将是一个糟糕的策略，因为它的选择性为 50%，因此数据库必须读取一半的表格，然后由于其他条件而拒绝大部分表格。首先使用最具选择性的标准可以提供更好的性能。因此，我模拟了概率分布。

所以我们有一百万用户，有 20 个属性;有些非常常见，例如在 50% 的用户中设置的属性 1，而另一些则很少见，例如仅在 2.4% 的用户中设置的属性 20。

VACUUM ANALYZE;

让我们对非常常见的属性 1 和 2 进行幼稚的搜索：

EXPLAIN ANALYZE SELECT *
    FROM users_attrs u1
    JOIN users_attrs u2 USING (user_id)
    WHERE u1.attr_id=1 AND u2.attr_id=2;

 Merge Join  (rows=125248)
   Merge Cond: (u1.user_id = u2.user_id)
   ->  Index Only Scan using users_attrs_au on users_attrs u1
         Index Cond: (attr_id = 1)
   ->  Index Only Scan using users_attrs_au on users_attrs u2
         Index Cond: (attr_id = 2)
 Execution Time: 93.706 ms

观察：

• 仅索引扫描通过允许高效的合并联接来节省时间。我在您的问题中没有看到任何索引，因此您应该尝试按该顺序在 （attribute_id，attribute_value，user_id） 上添加索引，因为这将允许搜索具有特定值的attribute_id（因为这些是前两列），然后直接获取user_id，甚至无需查看表格。

• 它相当慢（~100 毫秒）并且不能很好地扩展。

• 搜索返回 125k 行，这意味着它无用。用户将查看显示的页数，叹息并输入更有针对性的搜索条件。这意味着资源被浪费了（尤其是排序，我没有在查询中添加）。

现在，让我们搜索一个具有多个值的属性，我将通过搜索 id（1 或 2）和 3 来模拟这些属性。

 EXPLAIN ANALYZE SELECT *
    FROM users_attrs u1
    JOIN users_attrs u2 USING (user_id)
    WHERE u1.attr_id BETWEEN 1 AND 2 AND u2.attr_id=3;

 Hash Join  (rows=124719)
   Hash Cond: (u1.user_id = u2.user_id)
   ->  Index Only Scan using users_attrs_au on users_attrs u1
         Index Cond: ((attr_id >= 1) AND (attr_id <= 2))
   ->  Hash
         ->  Index Only Scan using users_attrs_au on users_attrs u2  
               Index Cond: (attr_id = 3)
 Execution Time: 151.845 ms

计划更改：在前一种情况下，索引将按顺序产生user_id，从而允许有效的合并连接。在这种情况下，它不会，因此 postgres 使用哈希。上述备注相同。

现在让我们搜索两个稀有属性。

EXPLAIN ANALYZE SELECT *
    FROM users_attrs u1
    JOIN users_attrs u2 USING (user_id)
    WHERE u1.attr_id=19 AND u2.attr_id=20;

 Merge Join  (cost=0.85..1565.88 rows=892 width=12) (actual time=0.223..9.917 rows=659 loops=1)
   Merge Cond: (u1.user_id = u2.user_id)
   ->  Index Only Scan using users_attrs_au on users_attrs u1  (cost=0.43..710.83 rows=24823 width=8) (actual time=0.096..3.495 rows=26570 loops=1)
         Index Cond: (attr_id = 19)
   ->  Index Only Scan using users_attrs_au on users_attrs u2  (cost=0.43..721.11 rows=25182 width=8) (actual time=0.030..3.284 rows=25103 loops=1)
         Index Cond: (attr_id = 20)
 Execution Time: 9.988 ms

这真是太好了。它速度很快，行数估计很好，最终结果是可用的：通过一些排序，用户应该会在那里找到一些东西。

请注意，破坏服务器的搜索查询始终是无用的。他们是那些试图返回大猩猩行的人，即使它工作得很快，也没有人会阅读结果。

现在让我们搜索一个常见属性和两个稀有属性。

EXPLAIN ANALYZE SELECT *
    FROM users_attrs u1
    JOIN users_attrs u2 USING (user_id)
    JOIN users_attrs u3 USING (user_id)
    WHERE u1.attr_id=1 AND u2.attr_id=19 AND u3.attr_id=20;

 Nested Loop  (cost=1.28..2674.39 rows=636 width=16) (actual time=0.173..9.837 rows=335 loops=1)
   ->  Merge Join  (cost=0.85..1565.88 rows=892 width=16) (actual time=0.117..8.189 rows=659 loops=1)
         Merge Cond: (u2.user_id = u3.user_id)
         ->  Index Only Scan using users_attrs_au on users_attrs u2  (cost=0.43..710.83 rows=24823 width=8) (actual time=0.041..2.838 rows=26570 loops=1)
               Index Cond: (attr_id = 19)
               Heap Fetches: 0
         ->  Index Only Scan using users_attrs_au on users_attrs u3  (cost=0.43..721.11 rows=25182 width=8) (actual time=0.012..2.660 rows=25103 loops=1)
               Index Cond: (attr_id = 20)
               Heap Fetches: 0
   ->  Index Only Scan using users_attrs_au on users_attrs u1  (cost=0.43..1.24 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=659)
         Index Cond: ((attr_id = 1) AND (user_id = u2.user_id))
         Heap Fetches: 0
 Planning Time: 1.449 ms
 Execution Time: 9.907 ms

这也很好。我故意将连接放在错误的顺序上：PG 注意到了这两个罕见的属性，并重新排序了连接以首先搜索它们（中间的合并连接返回 659 行）。然后，它检查生成的行是否具有 common 属性，保留 335 行。因此，它避免了扫描具有通用属性 #1 的 500k 行，这正是我们想要的。

在您的示例中，对于属性值，它有点复杂，因为 postgres 累积并由查询计划器使用的默认统计信息仅按列显示。因此，您可能希望启用 （attribute_id，attribute_value） 的多变量统计量以获得更好的估计值。

但最重要的是如上所述的正确索引。

如果您的属性值是固定的（即多项选择题），那么您可以为所有属性值对分配一个 ID 号，我的示例直接适用。

你的问题也完全映射到......全文搜索。您可以使用全文搜索引擎，它们正是为此进行了优化的。比如说，如果属性是 dog_name='rex'，你可以将用户的所有属性放在一个文本字段中，例如“dog_name__rex”的形式......并将其推入 Lucene 或 Xapian 中，在巨大的数据集上进行毫秒级搜索。

Postgres 确实有一个全文模块，但它并没有那么快。但是，如果您可以将问题映射到“为所有属性值对分配 ID 号”，则可以使用其后端，即模块 intarray：

CREATE UNLOGGED TABLE users_attrs_a( user_id INTEGER NOT NULL, attr_ids INTEGER[] );
INSERT INTO users_attrs_a SELECT user_id, array_agg(attr_id) FROM users_attrs GROUP BY user_id;
CREATE INDEX users_attrs_a_rdtree_idx ON users_attrs_a USING GIST (attr_ids gist__int_ops);
VACUUM ANALYZE users_attrs_a;
EXPLAIN ANALYZE SELECT * FROM users_attrs_a WHERE attr_ids @> '{1,19,20}';
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users_attrs_a  (cost=27.85..1438.56 rows=444 width=33) (actual time=7.224..7.770 rows=335 loops=1)
   Recheck Cond: (attr_ids @> '{1,19,20}'::integer[])
   Heap Blocks: exact=328
   ->  Bitmap Index Scan on users_attrs_a_rdtree_idx  (cost=0.00..27.74 rows=444 width=0) (actual time=7.197..7.197 rows=335 loops=1)
         Index Cond: (attr_ids @> '{1,19,20}'::integer[])
 Planning Time: 0.326 ms
 Execution Time: 7.846 ms

它的速度更快，扩展性更好。在你的桌子大小上，不值得麻烦。