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

iqxoj9l9 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(147)

我在Postgres数据库中有一个表users，它与另一个表users_attributes有一对多关系，这是一个简单的键值类型表，带有users表的外键列。

create table users(
  id: uuid primary key, 
  name: varchar
);

create table users_attributes(
  attribute_id: integer primary key,
  user_id: uuid references users(id),
  attribute_name: varchar, 
  attribute_value: varchar
);

字符串
我需要根据users_attributes表中的attribute_name和attribute_value过滤users。我已经尝试过这个查询，它可以工作，但执行时间要长得多：

select * from users u
left join users_attributes ua1 on u.id = ua1.user_id and ua1.attribute_name = 'dog_name'
left join users_attributes ua2 on u.id = ua2.user_id and ua2.attribute_name = 'cat_name'
where ua1.attribute_value = 'Spot' and ua2.attribute_value = 'Mittens';

型
对于我需要过滤用户的每个属性，连接的数量都会增加。这导致查询变慢（根据连接的数量在4-10秒之间），因为大约有10万个用户。查询上的解释计划支持这一点。
如何以更快的方式查询用户？

postgresql

来源：https://stackoverflow.com/questions/77473989/efficient-way-to-filter-the-one-side-by-values-in-the-many-side-in-a-one-to

2条答案

按热度按时间

t5zmwmid1#

这种类型的查询是典型的“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个属性;有些是非常频繁的，比如属性1，它在50%的用户中设置，而其他的是罕见的，比如20，它只在2.4%的用户中设置。

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 ms），并且扩展性不好。
搜索返回125 k行，这意味着它是无用的。用户将查看显示的页面数量，叹息，并输入更有针对性的搜索条件。这意味着资源被浪费了（特别是排序，我没有在查询中添加）。

现在让我们搜索一个具有多个值的属性，我将通过搜索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行）。然后它检查结果行是否具有公共属性，保留335行。因此它避免了扫描具有公共属性#1的500 k行，这正是我们想要的
在你的例子中，对于属性值，它有点复杂，因为postgres积累的和查询规划器使用的默认统计数据只是每列的。所以你可能想在（attribute_id，attribute_value）上启用multivariate statistics以获得更好的估计。
但最重要的是如上所述的正确指数。
如果每个属性的属性值是固定的（即多项选择题），那么你可以为所有的属性值对分配一个id号，我的例子直接适用。
你的问题也完全Map到.全文搜索。你可以使用全文搜索引擎，它们是专门为此优化的。比如，如果属性是dog_name ='rex'，你可以将用户的所有属性放在一个文本字段中，比如“dog_name_rex”.
Postgres确实有一个fulltext模块，但它不是那么快。但是，如果你可以将你的问题Map到“为所有属性-值对分配一个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

型
它更快一点，扩展性更好。在你的table大小上，不值得麻烦。

赞(0）回复(0）举报 2023-11-18

x3naxklr2#

LEFT JOIN和WHERE条件的混合在逻辑上是没有意义的。参见：

更详细地解释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';

字符串
基本上，这是一个relational-division的情况。
有很多方法可以做到这一点。最好的查询风格取决于你的基数，你的典型过滤器，以及你正在优化的内容。这里有一个完整的武器库：

如何过滤has-many-through关系中的SQL结果

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

Is a composite index also good for queries on the first field?的

查询将把属性名解析为整数ID（在查询中显式或隐式），并继续使用这些ID。

赞(0）回复(0）举报 2023-11-18

我来回答

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

2条答案

相关问题

热门标签

最新问答