匹配长列表中的任何值
架构：
# Table name: foos
#
# id :integer not null, primary key
# bar_ids :integer is an Array
#
# Indexes
#
# index_foos_on_bar_ids (bar_ids) USING gin
查询：
SELECT * FROM foos
WHERE (
bar_ids && '{
28151247,
17295392,
…300 more bar_ids…,
29368568,
45191356
}')
这会导致非常缓慢的顺序扫描。我尝试重写为一系列ORS，但这只是给了我平行的顺序扫描。将其分成较小的查询工作 - 在切换到效率低下的计划之前，它最多可以完成约70个任期 - 但需要运行更多查询。有什么方法可以编写查询以获取更有效的计划？
The schema:
# Table name: foos
#
# id :integer not null, primary key
# bar_ids :integer is an Array
#
# Indexes
#
# index_foos_on_bar_ids (bar_ids) USING gin
The query:
SELECT * FROM foos
WHERE (
bar_ids && '{
28151247,
17295392,
…300 more bar_ids…,
29368568,
45191356
}')
This results in a very slow sequential scan. I tried rewriting as a series of ORs, but that just gave me a parallel sequential scan. Splitting it up into smaller queries works—it can do up to about 70 terms before switching to an inefficient plan–but that requires running many more queries. Is there a way I can write the query to get a more efficient plan?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现的最好的解决方案是暂时禁用顺序扫描：
设置本地enable_seqscan ='off'';应该仅用于连接。您可以使用
show enable_seqscan;检查它。这迫使计划者利用index_foos_on_bar_ids索引并运行更快的查询。
另一个替代方案，我不推荐太多，因为它更复杂，语义更少，它将一个大查询分为许多较小的查询，所有查询都低于使用顺序扫描的阈值，并使用联合将它们合并回去一个查询。
The best solution I found was to temporarily disable sequential scans:
SET LOCAL enable_seqscan = 'off';That should last just for the connection. You can check it with
SHOW enable_seqscan;.That forced the planner to take advantage of the index_foos_on_bar_ids index and run a much faster query.
Another alternative, which I wouldn't recommend as much because it's more complicated and less semantic, is splitting the one big query up into many smaller queries, all of them below the threshold for using a sequential scan, and using UNION to merge them back into one query.