postgresql - 使用 order_by 和分页的 Flask-SqlAlchemy 查询非常慢

Question

我使用Python=2.7.3, PostgreSQL=9.3, Flask=0.10.0, SQLAlchemy =1.10.12，Flask-SQLAlchemy=2.1 和psycopg2=2.5.4

我在 PostgreSQL 中有一个 10 亿条记录的表，我必须对其进行分页并在其中提供搜索:

class MyTable(db.Model):
    """ My Table """
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(100), index=True, unique=True)
    name = db.Column(db.String(512), index=True)
    __tablename__ = 'my_table'

所以我在请求数据的代码中做了以下操作:

records = MyTable.query.filter(**filter_list).\
    order_by(asc('code')).paginate(page, per_page, False)

关键是，如果 per_page=10 和 page=1158960 即使根本没有过滤，也只需要 13 秒才能为最后一页选择 10 条最后记录。

根据我在 flask-sqlalchemy 源代码中发现的内容，.paginate 是这样做的:

.order_by(asc('code')).limit(per_page).offset((page - 1) * per_page)

生成的 SQL 查询如下所示:

SELECT my_table.id, my_table.code, my_table.name 
FROM my_table ORDER BY my_table.code ASC 
LIMIT 10 OFFSET 1158960

当我在服务器控制台上启动它时，我意识到问题出在 ORDER BY 子句中。不知何故，它必须首先使用 ORDER BY 对整个表进行排序，然后才使用 LIMIT 和 OFFSET。但这太慢了。

解释(分析):

"Limit  (cost=470520.26..470520.26 rows=1 width=178) (actual time=12460.060..12460.061 rows=8 loops=1)"
"  ->  Sort  (cost=467626.96..470520.26 rows=1157320 width=178) (actual time=11343.220..12424.686 rows=1158968 loops=1)"
"        Sort Key: code"
"        Sort Method: external merge  Disk: 218312kB"
"        ->  Seq Scan on my_table  (cost=0.00..42518.20 rows=1157320 width=178) (actual time=0.026..378.637 rows=1158968 loops=1)"
"Total runtime: 12475.160 ms"

如果您只是从该 SQL 请求中删除 ORDER BY，它会在 270 毫秒内完成!

"Limit  (cost=42518.20..42518.20 rows=1 width=178) (actual time=269.940..269.942 rows=8 loops=1)"
"  ->  Seq Scan on my_table  (cost=0.00..42518.20 rows=1157320 width=178) (actual time=0.030..246.200 rows=1158968 loops=1)"
"Total runtime: 269.992 ms"

我能做些什么吗？

Answer 1

好的，我找到了解决这个问题的方法。

当我执行完全相同的查询但使用 SET enable_seqscan=off; 时，它会强制 PostgreSQL 使用索引扫描而不是序列扫描，并且它变得很多更快!

SET enable_seqscan=off;
SELECT my_table.id, my_table.code, my_table.name 
FROM my_table ORDER BY my_table.code ASC 
LIMIT 10 OFFSET 1158960

**EXPLAIN (ANALYZE):**

"Limit  (cost=1814764.86..1814777.39 rows=8 width=131) (actual time=616.543..616.545 rows=8 loops=1)"
"  ->  Index Scan using ix_my_table_code on my_table  (cost=0.43..1814777.39 rows=1158968 width=131) (actual time=0.065..590.898 rows=1158968 loops=1)"
"Total runtime: 616.568 ms"

所以现在的重点是——如何设置 PostgreSQL 配置以使其在不强制使用索引扫描的情况下使用索引扫描？我想答案是 - “Planner Cost Constants”。对他们有什么建议吗？

2016 年 4 月 13 日更新:

我终于搞清楚了情况，找到了解决办法。在我的例子中，一切都通过在 postgresql.conf 中设置 Planner Cost Constants 来解决:

seq_page_cost = 1.0
random_page_cost = 1.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.0001
cpu_operator_cost = 0.0025
effective_cache_size = 1024MB

此外，还有许多建议将服务器整个 RAM 的 effective_cache_size 设置为 3/4。无论如何，通过这些设置，Planner 总是在大表上使用索引扫描。所以计时现在是 200-300 毫秒。

问题已解决。