PostgreSQL 范围过滤 + 其他字段排序OFFSET LIMIT(多字段区间过滤)的优化与加速

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , 范围过滤 , 其他字段排序 , 索引 , offset , limit


背景

在索引扫描中,如果两个字段扫描都是区间扫描,那么只能用到某个字段的过滤条件,另一个字段需要全扫描。

例如

create table t(id int, c1 int, c2 int);  
  
insert into t select generate_series(1,6000000), random()*10000, random()*10000;  
  
create index idx_t_1 on t(c1, c2);  
  
explain (analyze,verbose,timing,costs,buffers) select * from t where c1 between 1 and 10000 order by c2 limit 1;  
  
                                                                   QUERY PLAN                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=25496.76..25496.76 rows=1 width=12) (actual time=5330.577..5330.578 rows=1 loops=1)  
   Output: id, c1, c2  
   Buffers: shared hit=6021360  
   ->  Sort  (cost=25496.76..25571.75 rows=29997 width=12) (actual time=5330.576..5330.576 rows=1 loops=1)  
         Output: id, c1, c2  
         Sort Key: t.c2  
         Sort Method: top-N heapsort  Memory: 25kB  
         Buffers: shared hit=6021360  
         ->  Index Scan using idx_t_1 on public.t  (cost=0.43..25346.77 rows=29997 width=12) (actual time=0.032..4526.864 rows=5999724 loops=1)  
               Output: id, c1, c2  
               Index Cond: ((t.c1 >= 1) AND (t.c1 <= 10000))  
               Buffers: shared hit=6021360  
 Planning time: 0.100 ms  
 Execution time: 5330.734 ms  
(14 rows)  
  
explain (analyze,verbose,timing,costs,buffers) select * from t where c1 between 1 and 10000 and c2 between 100000 and 1111110 limit 1;  
                                                           QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.43..5.52 rows=1 width=12) (actual time=248.439..248.439 rows=0 loops=1)  
   Output: id, c1, c2  
   Buffers: shared hit=21828  
   ->  Index Scan using idx_t_1 on public.t  (cost=0.43..763.89 rows=150 width=12) (actual time=248.437..248.437 rows=0 loops=1)  
         Output: id, c1, c2  
         Index Cond: ((t.c1 >= 1) AND (t.c1 <= 10000) AND (t.c2 >= 100000) AND (t.c2 <= 1111110))  
         Buffers: shared hit=21828  
 Planning time: 0.120 ms  
 Execution time: 248.578 ms  
(9 rows)  
AI 代码解读

原因是在复合索引中,每一个复合KEY是有序的,但是多级下来,每一层并不是有序的。例如:

pic

那么前面的例子SQL2 实际上是层1用到了索引过滤,而层二则需要过滤。

例子SQL2 实际上是层1用到了索引过滤,并取出所有满足条件的行(因为用到了索引外的字段,所以不能用INDEX ONLY SCAN),再按层2排序。

索引结构可以参考:

《深入浅出PostgreSQL B-Tree索引结构》

那么如何优化这类SQL呢?

优化与场景

某个业务,数据包含了:

1、订单支付时间

2、订单号

3、唯一键 = 订单支付时间+订单号 (因为一个时间点,可能有多个订单)

业务需要取出大于某个支付时间,并按唯一键排序,偏移N条后,取一条。

DEMO表如下:

create table test(id int, c1 text, c2 timestamp);  
AI 代码解读

写入6000万订单

insert into test select id, c1::text||id, c1 from (select generate_series(1,10000000) id, clock_timestamp()::timestamp(1) c1) t;  
AI 代码解读

创建后期优化需要用到的索引

create index idx1 on test (c1);  
create index idx2 on test (c2);  
create index idx3 on test (c3);  
AI 代码解读

查询语句如下:

select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c1 limit 1 offset 50000;  
  
LOG:  duration: 943.851 ms  plan:  
Query Text: select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c1 limit 1 offset 50000;  
Limit  (cost=4714.82..4714.92 rows=1 width=40) (actual time=943.822..943.823 rows=1 loops=1)  
  Output: id, c1, c2  
  Buffers: shared hit=141577  
  ->  Index Scan using idx1 on public.test  (cost=0.56..339427.36 rows=3600000 width=40) (actual time=928.122..939.856 rows=50001 loops=1)  
        Output: id, c1, c2  
        Filter: (test.c2 >= '2018-01-22 16:13:25.4'::timestamp without time zone)  
        Rows Removed by Filter: 6281766  
        Buffers: shared hit=141577  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 944.485 ms  
AI 代码解读

性能并不理想。耗费了1秒左右的时间。原因是满足条件1的数据量太大,需要完全取出后排序再OFFSET。

优化方法

由于C1由C2+订单号组成,所以C1的最小值一定出现在C2中的最小值的区间中。那么可以这么优化

create or replace function get_test1(timestamp, int) returns test as $$  
declare  
  v1 timestamp;  
  v2 text;  
  res text;  
begin  
  set enable_seqscan=off;  
  
  -- 从输入的C2的条件,得到最小的C2  
  -- 索引精确定位  
  select c2 into v1 from test where c2 >= $1 order by c2 limit 1;  
    
  -- 在最小的C2中,求最小的C1  
  -- 索引精确定位  
  select min(c1) into v2 from test where c2 = v1;  
  
  -- 大于最小的C1,排序返回  
  -- 索引offset定位  
  select t into res from test t where c1 >= v2 order by c1 limit 1 offset $2 ;  
  return res::test;  
end;  
$$ language plpgsql strict;  
AI 代码解读

使用以上优化,每一步的开销都是最小的。效果如下:

postgres=# \timing  
postgres=# load 'auto_explain';  
LOAD  
postgres=# set auto_explain.log_nested_statements =on;  
SET  
Time: 0.165 ms  
postgres=# set auto_explain.log_analyze =on;  
SET  
Time: 0.168 ms  
postgres=# set auto_explain.log_buffers =on;  
SET  
Time: 0.144 ms  
postgres=# set auto_explain.log_min_duration =0;  
SET  
Time: 0.161 ms  
postgres=# set auto_explain.log_timing =on;  
SET  
Time: 0.147 ms  
postgres=# set auto_explain.log_verbose =on;  
SET  
Time: 0.173 ms  
  
  
postgres=# select * from get_test1('2018-01-22 16:13:25.4'::timestamp, 50000);  
LOG:  duration: 0.045 ms  plan:  
Query Text: select c2         from test where c2 >= $1 order by c2 limit 1  
Limit  (cost=0.43..0.46 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)  
  Output: c2  
  Buffers: shared hit=4  
  ->  Index Only Scan using idx2 on public.test  (cost=0.43..99814.16 rows=3333333 width=8) (actual time=0.041..0.041 rows=1 loops=1)  
        Output: c2  
        Index Cond: (test.c2 >= $1)  
        Heap Fetches: 1  
        Buffers: shared hit=4  
LOG:  duration: 0.024 ms  plan:  
Query Text: select min(c1)         from test where c2 = v1  
Result  (cost=0.96..0.97 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=1)  
  Output: $0  
  Buffers: shared hit=5  
  InitPlan 1 (returns $0)  
    ->  Limit  (cost=0.56..0.96 rows=1 width=28) (actual time=0.020..0.020 rows=1 loops=1)  
          Output: test.c1  
          Buffers: shared hit=5  
          ->  Index Only Scan using idx3 on public.test  (cost=0.56..31750.61 rows=79365 width=28) (actual time=0.020..0.020 rows=1 loops=1)  
                Output: test.c1  
                Index Cond: ((test.c2 = $4) AND (test.c1 IS NOT NULL))  
                Heap Fetches: 1  
                Buffers: shared hit=5  
LOG:  duration: 57.454 ms  plan:  
Query Text: select t          from test t where c1 >= v2 order by c1 limit 1 offset $2  
Limit  (cost=1699.64..1699.67 rows=1 width=92) (actual time=57.451..57.451 rows=1 loops=1)  
  Output: t.*, c1  
  Buffers: shared hit=774  
  ->  Index Scan using idx1 on public.test t  (cost=0.56..125732.85 rows=3700012 width=92) (actual time=0.048..53.365 rows=50001 loops=1)  
        Output: t.*, c1  
        Index Cond: (t.c1 >= '2018-01-22 16:13:25.46364521'::text)  
        Buffers: shared hit=774  
LOG:  duration: 58.163 ms  plan:  
Query Text: select * from get_test1('2018-01-22 16:13:25.4'::timestamp, 50000);  
Function Scan on public.get_test1  (cost=0.25..0.26 rows=1 width=44) (actual time=58.155..58.156 rows=1 loops=1)  
  Output: id, c1, c2  
  Function Call: get_test1('2018-01-22 16:13:25.4'::timestamp without time zone, 50000)  
  Buffers: shared hit=783  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 58.503 ms  
AI 代码解读

性能提升非常明显。

业务逻辑优化

实际上业务方要的是c2的值(支付时间),因此完全没必要按UK(c2+订单号)来排序,也就是说,SQL可以改写成这样。

postgres=# select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c2 limit 1 offset 50000;  
LOG:  duration: 14.945 ms  plan:  
Query Text: select * from test where c2 >= '2018-01-22 16:13:25.4'::timestamp order by c2 limit 1 offset 50000;  
Limit  (cost=1497.64..1497.67 rows=1 width=40) (actual time=14.932..14.933 rows=1 loops=1)  
  Output: id, c1, c2  
  Buffers: shared hit=608  
  ->  Index Scan using idx2 on public.test  (cost=0.43..107799.04 rows=3600000 width=40) (actual time=0.030..10.541 rows=50001 loops=1)  
        Output: id, c1, c2  
        Index Cond: (test.c2 >= '2018-01-22 16:13:25.4'::timestamp without time zone)  
        Buffers: shared hit=608  
   id    |              c1              |          c2             
---------+------------------------------+-----------------------  
 6414521 | 2018-01-22 16:13:25.46414521 | 2018-01-22 16:13:25.4  
(1 row)  
  
Time: 15.442 ms  
AI 代码解读

从业务层出发,改一条SQL,达到了最佳的效果。(而且最后只需要一个索引即可。)

从另一个层面来看这个优化,多个字段都是范围的查询,复合索引并不是最好的选择,更好的选择可能是:分区表+单索引,或者分区索引。需要内核的功能加强。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
1
0
0
20684
分享
相关文章
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
210 0
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
152 1
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
136 1
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
258 2
云原生数据仓库产品使用合集之原生数据仓库AnalyticDB PostgreSQL版如果是列存表的话, adb支持通过根据某个字段做upsert吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
322 7
PostgreSQL和greenplum的copy命令可以添加字段吗?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗?
107 3
postgresql如何将没有关联关系的两张表的字段合并
【6月更文挑战第2天】postgresql如何将没有关联关系的两张表的字段合并
179 3
实时计算 Flink版产品使用合集之使用PostgreSQL作为源时,遇到before字段为NULL该如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版