gpt4 book ai didi

mysql - Rails Slow Postgres Query with Order using Index

转载 作者:行者123 更新时间:2023-11-29 06:19:10 25 4
gpt4 key购买 nike

我正在查询包含几百万项的数据库,当我添加订单时,该数据库变得非常慢。这是我调用的代码:

Post.where(source_id: source_ids_array).page(1).per(100).order("position asc, external_created_at desc")

(我正在使用 Kaminari 进行分页)

这给了我下面的 sql:

Post Load (36537.8ms)  SELECT  "posts".* FROM "posts"  WHERE "posts"."source_id" IN (17805, 18768, 20717, 17803, 17804, 18329, 20705, 19075, 19110, 19082, 18328)  ORDER BY position asc, external_created_at desc LIMIT 100 OFFSET 0

但是,当我将查询修改为:

Post.where(source_id: source_ids_array).page(1).per(100).order("position asc")

我得到以下 sql:

Post Load (279.6ms)  SELECT  "posts".* FROM "posts"  WHERE "posts"."source_id" IN (17805, 18768, 20717, 17803, 17804, 18329, 20705, 19075, 19110, 19082, 18328)  ORDER BY position asc LIMIT 100 OFFSET 0

这是出奇的快。

我的 schema.db 中的索引如下所示:

add_index "posts", ["external_created_at"], name: "index_posts_on_external_created_at", using: :btree
add_index "posts", ["position", "external_created_at"], name: "index_posts_on_position_and_external_created_at", using: :btree
add_index "posts", ["position"], name: "index_posts_on_position", using: :btree

我怎样才能加快这个查询的速度?

编辑:这是我的解释分析:

Limit  (cost=633132.80..633133.05 rows=100 width=891) (actual time=31927.725..31927.751 rows=100 loops=1)
-> Sort (cost=633132.80..635226.42 rows=837446 width=891) (actual time=31927.720..31927.729 rows=100 loops=1)
Sort Key: "position", external_created_at
Sort Method: top-N heapsort Memory: 78kB
-> Bitmap Heap Scan on posts (cost=19878.94..601126.22 rows=837446 width=891) (actual time=487.399..30855.211 rows=858629 loops=1)
Recheck Cond: (source_id = ANY ('{17805,18768,20717,17803,17804,18329,20705,19075,19110,19082,18328}'::integer[]))
Rows Removed by Index Recheck: 1050547
-> Bitmap Index Scan on index_posts_on_source_id (cost=0.00..19669.58 rows=837446 width=0) (actual time=485.025..485.025 rows=927175 loops=1)
Index Cond: (source_id = ANY ('{17805,18768,20717,17803,17804,18329,20705,19075,19110,19082,18328}'::integer[]))
Total runtime: 31927.998 ms

最佳答案

虽然它的文档不是很好可以在创建索引时指定排序顺序:

add_index :posts, [:external_created_at, :position], 
order: { position: :asc, external_created_at: :desc }

如果我们随后运行 rake db:structure:dump,我们可以看到它创建了以下 SQL:

CREATE INDEX "index_posts_on_external_created_at_and_position" 
ON "posts" ("external_created_at" DESC, "position" ASC);

请注意,我们不需要指定 using: :btree,因为 Postgres 默认使用 B-tree 或 name:

关于mysql - Rails Slow Postgres Query with Order using Index,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34259882/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com