postgresql 对于每个数组元素，计算数组列包含该元素的行数

nc1teljy 于 2023-05-17 发布在 PostgreSQL

关注(0)|答案(1)|浏览(173)

在表users中，有一列user_ids varchar[]具有GIN索引。每行中列user_ids的每个user_id是唯一的。
我想对输入列表中的每个user_id计算包含user_id的行数。
我尝试使用这个查询，但EXPLAIN显示它不使用GIN索引：

SELECT user_id, count(*) + 1 COUNT
FROM (
    SELECT unnest(user_ids) user_id
    FROM users 
    WHERE user_ids && ARRAY['1', '2', '3']::varchar[]
) sub
WHERE user_id = ANY(ARRAY['1', '2', '3']::varchar[])
GROUP BY user_id;

有没有办法在这里使用GIN索引？

postgresql

来源：https://stackoverflow.com/questions/76226976/for-each-array-element-count-rows-whose-array-column-contains-it

1条答案

按热度按时间

6ss1mwsb1#

对于较小的输入数组和行之间的有限重叠，此查询应该尽可能快：

SELECT i.user_id, u.*
FROM   unnest('{1, 2, 3}'::varchar[]) i(user_id)  -- your input here
CROSS  JOIN LATERAL (
   SELECT count(*) AS count
   FROM   users u
   WHERE  u.user_ids @> ARRAY[i.user_id]  -- !
   ) sub;

显著差异：我的查询包括输入的元素与0匹配，你的原始不匹配。
数组操作符@>期望数组类型作为左 * 和 * 右操作数（即使它只是像本例中那样的一个元素）。
数组运算符可以在(user_ids)上使用GIN索引。原始查询中的子查询也可以使用索引！
参见：

检查值是否存在于Postgres数组中
PostgreSQL可以索引数组列吗？

为什么我的查询计划不使用GIN索引？

也许Postgres决定使用不同的查询计划，因为过滤器的选择性不够，或者列统计信息已经过时了？
要确定索引是否完全适用，请使用此本地设置运行测试（仅在会话中！）：

SET enable_seqscan = off;

EXPLAIN ....

如果可能的话，这将强制执行没有顺序扫描的计划。

赞(0）回复(0）举报 2023-05-17

我来回答

postgresql 对于每个数组元素，计算数组列包含该元素的行数

1条答案

为什么我的查询计划不使用GIN索引？

相关问题

热门标签

最新问答