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

iqxoj9l9  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(161)

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

  1. create table users(
  2. id: uuid primary key,
  3. name: varchar
  4. );
  5. create table users_attributes(
  6. attribute_id: integer primary key,
  7. user_id: uuid references users(id),
  8. attribute_name: varchar,
  9. attribute_value: varchar
  10. );

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

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


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

t5zmwmid

t5zmwmid1#

这种类型的查询是典型的“craigslist查询”,基于属性(制造商,型号等)进行搜索......它也可以应用于约会网站,例如。
让我们建立一些测试数据。

  1. CREATE UNLOGGED TABLE users( user_id INTEGER NOT NULL );
  2. INSERT INTO users SELECT generate_series( 1, 1000000 );
  3. ALTER TABLE users ADD PRIMARY KEY( user_id );
  4. CREATE UNLOGGED TABLE users_attrs(
  5. user_id INTEGER NOT NULL,
  6. attr_id INTEGER NOT NULL );
  7. INSERT INTO users_attrs SELECT user_id, aid FROM (
  8. SELECT user_id, aid, 0.5/aid > random() x
  9. FROM generate_series(1,20) aid CROSS JOIN users ) foo
  10. WHERE x;
  11. ALTER TABLE users_attrs ADD PRIMARY KEY (user_id,attr_id);
  12. CREATE INDEX users_attrs_au ON users_attrs( attr_id, user_id );
  13. SELECT attr_id,count(*) FROM users_attrs GROUP BY 1 ORDER BY 2;
  14. attr_id | count
  15. ---------+--------
  16. 20 | 25104
  17. 19 | 26570
  18. 18 | 27638
  19. 17 | 29574
  20. 16 | 30982
  21. 15 | 33490
  22. 14 | 35574
  23. 13 | 38473
  24. 12 | 41816
  25. 11 | 45373
  26. 10 | 49641
  27. 9 | 55793
  28. 8 | 62471
  29. 7 | 71386
  30. 6 | 83123
  31. 5 | 99592
  32. 4 | 124920
  33. 3 | 166107
  34. 2 | 250662
  35. 1 | 500446

字符串
我没有把属性名放在users_attrs中,因为这应该放在一个单独的表中。
为了简单起见,我没有使用属性值。无论我们在(attribute_id,user_id)还是(attribute_id,attribute_value,user_id)上使用索引,结果都是相同的,以便于性能测量。在搜索时,重要的是概率分布,换句话说,搜索条件的选择性。
例如,假设你在一个约会网站上寻找“你附近25-30岁的女性”。首先根据“性别”进行搜索是一个糟糕的策略,因为它的选择性为50%,所以数据库将不得不读取一半的表,然后由于其他标准而拒绝大部分。首先使用最具选择性的标准会带来更好的性能。因此,我模拟了概率分布。
所以我们有一百万用户,有20个属性;有些是非常频繁的,比如属性1,它在50%的用户中设置,而其他的是罕见的,比如20,它只在2.4%的用户中设置。

  1. VACUUM ANALYZE;


让我们对非常常见的属性1和2进行简单的搜索:

  1. EXPLAIN ANALYZE SELECT *
  2. FROM users_attrs u1
  3. JOIN users_attrs u2 USING (user_id)
  4. WHERE u1.attr_id=1 AND u2.attr_id=2;
  5. Merge Join (rows=125248)
  6. Merge Cond: (u1.user_id = u2.user_id)
  7. -> Index Only Scan using users_attrs_au on users_attrs u1
  8. Index Cond: (attr_id = 1)
  9. -> Index Only Scan using users_attrs_au on users_attrs u2
  10. Index Cond: (attr_id = 2)
  11. Execution Time: 93.706 ms


观察结果:

  • 在你的问题中我没有看到任何索引,所以你应该尝试在(attribute_id,attribute_value,user_id)上添加一个索引,按照这个顺序,因为这将允许搜索具有特定值的attribute_id(因为这是前两列),然后直接获取user_id,甚至不需要查看表。
  • 它相当慢(~ 100 ms),并且扩展性不好。
  • 搜索返回125 k行,这意味着它是无用的。用户将查看显示的页面数量,叹息,并输入更有针对性的搜索条件。这意味着资源被浪费了(特别是排序,我没有在查询中添加)。

现在让我们搜索一个具有多个值的属性,我将通过搜索id(1或2)和3来模拟。

  1. EXPLAIN ANALYZE SELECT *
  2. FROM users_attrs u1
  3. JOIN users_attrs u2 USING (user_id)
  4. WHERE u1.attr_id BETWEEN 1 AND 2 AND u2.attr_id=3;
  5. Hash Join (rows=124719)
  6. Hash Cond: (u1.user_id = u2.user_id)
  7. -> Index Only Scan using users_attrs_au on users_attrs u1
  8. Index Cond: ((attr_id >= 1) AND (attr_id <= 2))
  9. -> Hash
  10. -> Index Only Scan using users_attrs_au on users_attrs u2
  11. Index Cond: (attr_id = 3)
  12. Execution Time: 151.845 ms


计划变更:在前面的例子中,索引会按顺序产生user_id,允许有效的合并连接。在这种情况下,它不会,所以postgres使用哈希。上面的注解同样适用。
现在让我们搜索两个稀有属性。

  1. EXPLAIN ANALYZE SELECT *
  2. FROM users_attrs u1
  3. JOIN users_attrs u2 USING (user_id)
  4. WHERE u1.attr_id=19 AND u2.attr_id=20;
  5. Merge Join (cost=0.85..1565.88 rows=892 width=12) (actual time=0.223..9.917 rows=659 loops=1)
  6. Merge Cond: (u1.user_id = u2.user_id)
  7. -> 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)
  8. Index Cond: (attr_id = 19)
  9. -> 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)
  10. Index Cond: (attr_id = 20)
  11. Execution Time: 9.988 ms


这非常好,它速度很快,行计数估计很好,最终结果是可用的:通过一点排序,用户应该可以在其中找到一些东西。
请注意,那些破坏你的服务器的搜索查询总是无用的,它们试图返回一个巨大的行,即使它运行得很快,也没有人会读到结果。
现在让我们搜索一个常见属性和两个罕见属性。

  1. EXPLAIN ANALYZE SELECT *
  2. FROM users_attrs u1
  3. JOIN users_attrs u2 USING (user_id)
  4. JOIN users_attrs u3 USING (user_id)
  5. WHERE u1.attr_id=1 AND u2.attr_id=19 AND u3.attr_id=20;
  6. Nested Loop (cost=1.28..2674.39 rows=636 width=16) (actual time=0.173..9.837 rows=335 loops=1)
  7. -> Merge Join (cost=0.85..1565.88 rows=892 width=16) (actual time=0.117..8.189 rows=659 loops=1)
  8. Merge Cond: (u2.user_id = u3.user_id)
  9. -> 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)
  10. Index Cond: (attr_id = 19)
  11. Heap Fetches: 0
  12. -> 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)
  13. Index Cond: (attr_id = 20)
  14. Heap Fetches: 0
  15. -> 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)
  16. Index Cond: ((attr_id = 1) AND (user_id = u2.user_id))
  17. Heap Fetches: 0
  18. Planning Time: 1.449 ms
  19. 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

  1. CREATE UNLOGGED TABLE users_attrs_a( user_id INTEGER NOT NULL, attr_ids INTEGER[] );
  2. INSERT INTO users_attrs_a SELECT user_id, array_agg(attr_id) FROM users_attrs GROUP BY user_id;
  3. CREATE INDEX users_attrs_a_rdtree_idx ON users_attrs_a USING GIST (attr_ids gist__int_ops);
  4. VACUUM ANALYZE users_attrs_a;
  5. EXPLAIN ANALYZE SELECT * FROM users_attrs_a WHERE attr_ids @> '{1,19,20}';
  6. --------------------------------------------------------------------------------------------------------------------------------------
  7. 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)
  8. Recheck Cond: (attr_ids @> '{1,19,20}'::integer[])
  9. Heap Blocks: exact=328
  10. -> 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)
  11. Index Cond: (attr_ids @> '{1,19,20}'::integer[])
  12. Planning Time: 0.326 ms
  13. Execution Time: 7.846 ms


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

展开查看全部
x3naxklr

x3naxklr2#

LEFT JOINWHERE条件的混合在逻辑上是没有意义的。参见:

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

基本重写:

  1. SELECT *
  2. FROM users u
  3. JOIN users_attributes ua1 ON u.id = ua1.user_id
  4. JOIN users_attributes ua2 ON u.id = ua2.user_id
  5. WHERE ua1.attribute_name = 'dog_name'
  6. AND ua1.attribute_value = 'Spot'
  7. AND ua2.attribute_name = 'cat_name'
  8. AND ua2.attribute_value = 'Mittens';

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

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

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

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

展开查看全部

相关问题