database - 提高 PostgreSQL 中的 OFFSET 性能

Question

我有一个表，我在 LIMIT 和 OFFSET 之前执行 ORDER BY 以进行分页。

在 ORDER BY 列上添加索引会对性能产生巨大影响(当与较小的 LIMIT 结合使用时)。在一个 500,000 行的表上，我看到添加索引后性能提高了 10,000 倍，只要有一个小的 LIMIT。

但是，该索引对高 OFFSET 没有影响(即我分页中的后面的页面)。这是可以理解的:b 树索引可以很容易地从头开始按顺序迭代，但找不到第 n 项。

似乎计数的 b 树索引 会有所帮助，但我不知道 PostgreSQL 是否支持这些索引。还有其他解决方案吗？似乎针对大型 OFFSET 进行优化(尤其是在分页用例中)并不罕见。

不幸的是，PostgreSQL 手册只是说“被 OFFSET 子句跳过的行仍然需要在服务器内部计算；因此大的 OFFSET 可能效率低下。”

Answer 1

您可能需要计算索引。

让我们创建一个表:

create table sales(day date, amount real);

并用一些随机的东西填充它:

insert into sales 
    select current_date + s.a as day, random()*100 as amount
    from generate_series(1,20);

按天索引，这里没什么特别的:

create index sales_by_day on sales(day);

创建一个行位置函数。还有其他方法，这是最简单的方法:

create or replace function sales_pos (date) returns bigint 
   as 'select count(day) from sales where day <= $1;' 
   language sql immutable;

检查它是否有效(尽管不要在大型​​数据集上这样调用它):

select sales_pos(day), day, amount from sales;

     sales_pos |    day     |  amount  
    -----------+------------+----------
             1 | 2011-07-08 |  41.6135
             2 | 2011-07-09 |  19.0663
             3 | 2011-07-10 |  12.3715
    ..................

现在是棘手的部分:添加另一个根据 sales_pos 函数值计算的索引:

create index sales_by_pos on sales using btree(sales_pos(day));

这里是你如何使用它。 5 是你的“偏移量”，10 是“限制”:

select * from sales where sales_pos(day) >= 5 and sales_pos(day) < 5+10;

        day     | amount  
    ------------+---------
     2011-07-12 | 94.3042
     2011-07-13 | 12.9532
     2011-07-14 | 74.7261
    ...............

它很快，因为当你这样调用它时，Postgres 使用索引中的预先计算值:

explain select * from sales 
  where sales_pos(day) >= 5 and sales_pos(day) < 5+10;

                                    QUERY PLAN                                
    --------------------------------------------------------------------------
     Index Scan using sales_by_pos on sales  (cost=0.50..8.77 rows=1 width=8)
       Index Cond: ((sales_pos(day) >= 5) AND (sales_pos(day) < 15))

希望对您有所帮助。