sql - 加速在两列上过滤并在函数上排序的 postgres 查询

Question

更新:我已经为我的案例找到了一个解决方案(我在下面的回答)涉及明智地使用“in”，但可能还有更普遍有用的建议.

我确信这个问题的答案在很多地方都存在，但我很难找到它，因为我的情况比我在 Postgres 文档中找到的讨论稍微复杂一些，但比我在这里发现的任何涉及多个表或子查询的问题，并通过详尽的攻击计划得到解答。因此，我不介意被指出我未能找到的现有答案之一，只要它确实对我的情况有所帮助即可。

这是给我带来麻烦的查询示例:

SELECT trees.id FROM "trees" WHERE "trees"."trashed" = 'f' AND (trees.chapter_id IN (1,8,9,12,18,11,6,10,5,2,4,7,16,15,17,3,14,13)) ORDER BY LOWER(trees.shortcode);

这是由我的 Rails 应用程序中的 ActiveRecord 生成的，也许我可以将查询改写为以某种方式更优化，但是这个结果集(所有树的 ID，按文本顺序，按“垃圾”过滤并属于“章节”的子集)是我目前需要的，用于界面中的大型分页树列表。 (章节的子集由用户权限系统决定，因此当用户开始查看列表时，必须至少调用一次此查询。)

在我的本地版本中，此表中大约有 67,000 棵树，并且在生产中只会更多。

这是 EXPLAIN 给出的查询计划:

Sort  (cost=9406.85..9543.34 rows=54595 width=17)
  Sort Key: (lower((shortcode)::text))
  ->  Seq Scan on trees  (cost=0.00..3991.18 rows=54595 width=17)
        Filter: ((NOT trashed) AND (chapter_id = ANY ('{1,8,9,12,18,11,6,10,5,2,4,7,16,15,17,3,14,13}'::integer[])))

如果我删除顺序，这会变得更快，显然，但同样，我需要以特定顺序排列的 ID 列表，以显示该列表的一页。在本地，这个查询执行大约需要 2-3 秒，这太长了，而且通常我发现生产版本所在的 heroku 上的数据库与我的本地数据库花费的时间相似或更长。

trees.trashed、trees.chapter_id 和 LOWER(trees.shortcode) 上有单独的 (btree) 索引。我尝试在 trashed 和 chapter_id 上添加多列索引，但可以预见的是，这没有帮助，因为这不是该查询的缓慢部分。我对 postgres 或 SQL 的了解还不够，不知道从这里到哪里去，这就是我寻求帮助的原因。 (我想了解更多，因此也非常感谢任何指向文档部分的指针，这些部分可以让我更好地了解要调查的事物的种类。)

章节列表永远不会比这长得多，所以单独过滤每个章节可能会更快？应用中其他地方也有类似的查询，所以我宁愿学习一种通用的方法来改进这种东西。

我可能在写这篇文章时忘记添加一些重要信息，所以如果有明显错误的地方，请发表评论，我会尽力澄清。

更新:应评论者的要求，这是树表的描述。

                                     Table "public.trees"
      Column       |            Type             |                     Modifiers                      
-------------------+-----------------------------+----------------------------------------------------
 id                | integer                     | not null default nextval('trees_id_seq'::regclass)
 created_at        | timestamp without time zone | 
 updated_at        | timestamp without time zone | 
 shortcode         | character varying(255)      | 
 cross_id          | integer                     | 
 chapter_id        | integer                     | 
 name              | character varying(255)      | 
 classification    | character varying(255)      | 
 tag               | character varying(255)      | 
 alive             | boolean                     | not null default true
 latitude          | numeric(14,10)              | 
 longitude         | numeric(14,10)              | 
 city              | character varying(255)      | 
 county            | character varying(255)      | 
 state             | character varying(255)      | 
 comments          | text                        | 
 trashed           | boolean                     | not null default false
 created_by_id     | integer                     | 
 death_date        | date                        | 
 planted_as        | character varying(255)      | not null default 'seed'::character varying
 wild              | boolean                     | not null default false
 submitted_by_id   | integer                     | 
 owned_by_id       | integer                     | 
 steward_id        | integer                     | 
 planting_id       | integer                     | 
 planting_cross_id | integer                     | 
Indexes:
    "trees_pkey" PRIMARY KEY, btree (id)
    "index_trees_on_chapter_id" btree (chapter_id)
    "index_trees_on_created_by_id" btree (created_by_id)
    "index_trees_on_cross_id" btree (cross_id)
    "index_trees_on_trashed" btree (trashed)
    "trees_lower_classification_idx" btree (lower(classification::text))
    "trees_lower_name_idx" btree (lower(name::text))
    "trees_lower_shortcode_idx" btree (lower(shortcode::text))
    "trees_lower_tag_idx" btree (lower(tag::text))

我的本​​地树表有 67406 行，生产中会有更多行。

Answer 1

根据您的查询计划，您将获取 67,000 行中的 55,000 行。没有索引可以帮助您做到这一点。最快的计划是读取整个表格，过滤掉偶尔不需要的行，然后排序。

自然地，真正的问题是您是否应该从一开始就获取那么多行，而不是使用 limit ... offset 对它们进行分页。在后一种情况下，您的索引将变得有用。特别是 lower(shortcode) 上的那个，因为它会非常快速地找到匹配的行，并且以正确的顺序进行。