Rails ActiveRecord下PostgreSQL百万级自定义属性查询优化问询

1. 先把SQL本身的无效开销砍干净 #

你现在的查询慢，很大程度是INTERSECT的写法带的坑：这个语法相当于对同一张大表跑两次独立的子查询，分别把两个子查询的结果排序、去重之后，再逐行对比算交集。在没有索引做快速过滤的场景下，两次全表扫描的IO、排序的CPU开销会直接把耗时拉上去。
完全可以把这个INTERSECT的等价逻辑改成一次单表扫+分组条件判断的写法，让数据库只扫一次表就搞定所有匹配：

SELECT attributable_id
FROM custom_attribute_values
WHERE 
  (custom_attribute_id = 12 AND value_string IN ('Female', 'Male'))
  OR (custom_attribute_id = 17 AND value_string IN ('Widowed', 'Divorced', 'Never married', 'Married', 'Separated'))
GROUP BY attributable_id
HAVING 
  -- 判断同一个attributable_id是否同时满足两个属性的筛选要求
  MAX(CASE WHEN custom_attribute_id = 12 THEN 1 ELSE 0 END) = 1
  AND MAX(CASE WHEN custom_attribute_id = 17 THEN 1 ELSE 0 END) = 1;

这种写法的实际收益：

• 把原来两次全表扫直接合并成1次，砍掉近一半的表扫描IO开销
• 不需要对两个子查询的结果做排序去重再算交集，数据库可以用哈希聚合的方式跑分组，把原来O(nlogn)的排序开销降成O(n)的线性判断
  小提示：如果custom_attribute_id实际是整数类型，记得把原来SQL里值两边的单引号去掉，避免隐式类型转换拖慢逐行判断的速度

2. 别让应用层做无意义的全量数据拉取 #

你现在Rails代码里用find_by_sql(segmentation_query).size算结果的写法，属于典型的性能浪费：这个方法会把SQL返回的所有attributable_id全部从数据库传到Rails进程里，还要实例化成ActiveRecord对象，最后在Ruby层面遍历数个数。要是符合条件的id有个几十万个，光是数据传输、对象初始化的开销就占一半以上的响应时间，还会平白占掉一堆应用内存。
直接把计数逻辑下推到数据库层就行，根本不需要把明细id拉到应用层，只要数据库返回一个最终的计数值就够：

def fetch_members
  begin
    # 让数据库直接算完总计数返回，应用层不需要额外做计算
    @filter_result = CustomAttributeValue.connection.select_value("
      SELECT COUNT(DISTINCT attributable_id) 
      FROM (#{上面重构后的分组查询SQL}) as matched_ids
    ")
  rescue Exception => e
    render_json_response(response, @success, e.message, e)
  end
end

就这一个改动，通常就能再把响应时间压掉30%-40%。

3. 用轻量预计算替代实时全表扫 #

如果你的动态筛选参数是固定的几个（比如就是性别、婚姻状态这类常用的自定义属性值），根本没必要每次用户触发查询都实时扫原始大表算结果。可以做个异步的预计算同步：

• 单独建一张轻量的映射表，存每个attributable_id对应的全量自定义属性键值对，相当于把长表拍平成一行一个对象的快照表
• 每当原始的custom_attribute_values表有写入、修改、删除操作时，异步触发更新这张映射表的对应记录，不影响主流程的写入性能
  这样用户触发筛选的时候，只需要扫这张轻量的快照表就行，根本不用碰原始的大宽表。这种方式的维护成本远低于在原始大表上加多字段联合索引，毕竟异步同步的逻辑不会占用在线表的写入资源。

内容的提问来源于stack exchange，提问作者Adnan