分页显示是web开发常见需求,随着表数据增加,200万以上时,翻页越到后面越慢,这个时候慢查询成为一个痛点,关于count(*)慢的原因,简单说会进行全表扫描,再排序,导致查询变慢。这里介绍postgresql一种解决方案。对于大表,我们有时候并不需要返回精确的数值,可以采用模糊的总数代替。
原始语句
SELECT COUNT(*) AS "__count" FROM "my_table"
优化语句
SELECT reltuples::numeric FROM pg_class WHERE relname = table_name
介绍Django admin 分页优化
from django.contrib.auth.admin import UserAdmin
from django.core.paginator import Paginator, cached_property
from django.db import connectionsclass UserAdmin(UserAdmin):
paginator = TablePaginatorclass TablePaginator(Paginator): @cached_property
def count(self):
return (
self._get_pgsql_estimate_count()
if not self.object_list.query.where
else super(LargeTablePaginator, self).count
) def _get_pgsql_estimate_count(self):
with connections["default"].cursor() as cursor:
cursor.execute(
"SELECT reltuples::numeric FROM pg_class WHERE relname = %s",
[self.object_list.query.model._meta.db_table],
) total_count = int(cursor.fetchone()[0])
return total_count