时间戳

首页 > 解决方案 > 加快代价高昂的连接操作 - 大表和小表

postgresql - 加快代价高昂的连接操作 - 大表和小表

问题描述

我正在使用 Postgres 存储大量事务并尝试将特定 Select 语句的读取时间保持在数十毫秒内。

TableA 的架构（> 100mm 行）：（userID int，itemID int）。由用户 ID 索引

TableB 的架构（1mm 行）：（categoryID int，itemID int）。按类别 ID 索引。类别数 = 500，每个 itemID 只属于一个类别。

我要优化的查询目前需要大约 100 毫秒才能执行是：

select * from TableA 
where userID = x and itemID in 
(select itemID from TableB
where categoryID = y)

解决此问题的一种简单方法是创建一个非规范化表，其中 userID、itemID 和 categoryID 作为列并在 (userID, categoryID) 上建立索引。但是，categoryID -> itemID 映射可能会发生变化，因此我希望避免对表进行全面扫描并在每次发生这种情况时更新行。

有没有其他技术/索引方法来加速这个 JOIN 操作？任何安排数据的替代方式也将不胜感激。谢谢！

编辑：添加示例查询计划。

[('  ->  Hash Semi Join  (cost=159.50..382.67 rows=164 width=50)'),
 ('        Hash Cond: (tableA.itemId = tableB.itemId)'),
 ('        ->  Index Scan using userId on tableA  (cost=0.57..208.31 rows=5185 width=50)'),
 ('              Index Cond: (userId = 4000)'),
 ('        ->  Hash  (cost=117.05..117.05 rows=3350 width=4)'),
 ('              Buckets: 4096  Batches: 1  Memory Usage: 161kB',),
 ('              ->  Index Scan using categoryId on tableB (cost=0.42..117.05 rows=3350 width=4)'),
 ('                    Index Cond: (categoryId = 1002)',), ('Planning time: 0.149 ms',)]

标签： postgresqlindexing

解决方案

也许 Exists 在这里会有所帮助： EXISTS 和 IN 之间的区别

对于您的查询：

Select * from TableA a
Where userID = x
and exists (Select itemId from TableB b where categoryID = y  and a.itemId = b.itemId)

推荐阅读