GiST索引致命弱点 | 排序和过滤不能同时进行
digoal约 7 分钟文中参考文档可点击阅读原文打开, 推荐《最好的PostgreSQL学习镜像》。
《往期吐槽回顾》
第50期吐槽: 优化器有点弱, 排序和过滤居然不能同时采用GiST索引
视频回放
1、产品的问题点
PG GiST距离排序操作符和过滤无法同时使用索引
2、问题点背后涉及的技术原理
PG GiST索引支持空间距离排序, 例如按距离某个经纬度点的距离排序, 返回表里面的经纬度点. PG 支持距离操作符, 支持排序功能, 同时支持返回距离值. 但是距离过滤不能使用索引
例子:
《PostgreSQL GiST Order by 距离 + 距离范围判定 + limit 骤变优化与背景原因》
create extension btree_gist;
postgres=# \do
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
--------+------+-----------------------------+-----------------------------+------------------+-------------
public | <-> | bigint | bigint | bigint |
public | <-> | date | date | integer |
public | <-> | double precision | double precision | double precision |
public | <-> | integer | integer | integer |
public | <-> | interval | interval | interval |
public | <-> | money | money | money |
public | <-> | oid | oid | oid |
public | <-> | real | real | real |
public | <-> | smallint | smallint | smallint |
public | <-> | time without time zone | time without time zone | interval |
public | <-> | timestamp with time zone | timestamp with time zone | interval |
public | <-> | timestamp without time zone | timestamp without time zone | interval | create table t_age(id int, age int);
insert into t_age select generate_series(1,10000000), random()*120;
create index idx_t_age_1 on t_age using gist (age);
select * from t_age
where
(age <-> 25) < 1
order by age <-> 25
limit 100000;
Limit (cost=0.42..10245.61 rows=100000 width=12) (actual time=0.161..8126.988 rows=83248 loops=1)
Output: id, age, ((age <-> 25))
Buffers: shared hit=9523157
-> Index Scan using idx_t_age_1 on public.t_age (cost=0.42..341506.11 rows=3333326 width=12) (actual time=0.160..8115.150 rows=83248 loops=1)
Output: id, age, (age <-> 25)
Order By: (t_age.age <-> 25)
Filter: ((t_age.age <-> 25) < 1)
Rows Removed by Filter: 9916752
Buffers: shared hit=9523157
Planning Time: 0.077 ms
Execution Time: 8133.808 ms
(11 rows)
postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from t_age where (age <-> 25) <1 limit 100000;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=10000000000.00..10000005827.44 rows=100000 width=8)
-> Seq Scan on t_age (cost=10000000000.00..10000194247.66 rows=3333326 width=8)
Filter: ((age <-> 25) < 1)
(3 rows)
create extension postgis; create table t_pos(
id int primary key,
pos geometry
);
insert into t_pos
select * from (
select id,
ST_SetSRID(
ST_Point( round((random()*(135.085831-73.406586)+73.406586)::numeric,6),
round((random()*(53.880950-3.408477)+3.408477)::numeric,6)
),
4326
) as pos
from generate_series(1,1000000000) t(id)
) t
order by st_geohash(pos,15);
create index idx_t_pos_1 on t_pos using gist(pos);
select *,
st_distancespheroid(pos, st_setsrid(st_makepoint(120,50),4326), 'SPHEROID["WGS84",6378137,298.257223563]') as dist
from t_pos
where
st_distancespheroid(pos, st_setsrid(st_makepoint(120,50),4326), 'SPHEROID["WGS84",6378137,298.257223563]') < 5000
order by pos <-> st_setsrid(st_makepoint(120,50),4326)
limit 100;
骤变发生在limit N超过所有符合条件的记录条数M时.
postgres=# explain analyze select * from t_age where (age <-> 25) < 1 order by age <-> 25 limit 825;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.28..80.98 rows=825 width=12) (actual time=0.074..40.244 rows=824 loops=1)
-> Index Scan using idx_t_age_1 on t_age (cost=0.28..3260.91 rows=33333 width=12) (actual time=0.073..40.119 rows=824 loops=1)
Order By: (age <-> 25)
Filter: ((age <-> 25) < 1)
Rows Removed by Filter: 99176
Planning Time: 0.068 ms
Execution Time: 40.340 ms
(7 rows)postgres=# explain analyze select * from t_age where (age <-> 25) < 1 order by age <-> 25 limit 824;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.28..80.88 rows=824 width=12) (actual time=0.075..0.798 rows=824 loops=1)
-> Index Scan using idx_t_age_1 on t_age (cost=0.28..3260.91 rows=33333 width=12) (actual time=0.074..0.696 rows=824 loops=1)
Order By: (age <-> 25)
Filter: ((age <-> 25) < 1)
Planning Time: 0.066 ms
Execution Time: 0.880 ms
(6 rows)
因为排序它不懂距离value, 只知道距离是越来越大(即使排序和计算的操作符一模一样).
3、这个问题将影响哪些行业以及业务场景
影响最大的时基于地理位置的互联网业务, 例如社交、O2O、出行等
4、会导致什么问题?
当需要搜索附近的N个点, 并且距离M以内的双重需求时, 不能同时使用1个索引来满足. (要么只能用于排序, 要么只能用于where过滤) 需要额外的filter计算, 如果满足条件(距离M以内)的记录数不足N条, 则导致扫描整个索引, 性能急剧下降.
5、业务上应该如何避免这个坑
可以使用function来解决这个问题, 每次filter判定是否已越界. 《PostgreSQL GiST Order by 距离 + 距离范围判定 + limit 骤变优化与背景原因》
6、业务上避免这个坑牺牲了什么, 会引入什么新的问题
需要自定义函数, 开发成本增加.
7、数据库未来产品迭代如何修复这个坑
希望能直接在内核层面支持, 同一个gist index既能支持按距离过滤又能支持按距离排序输出. PGvector通过迭代扫描解决了这个问题, 期待PG高版本能解决.
文末彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航老司机开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: