【重新发现PostgreSQL之美】- 44 摩斯电码

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 大家好,这里是重新发现PostgreSQL之美 - 44 摩斯电码

背景


场景:
某些字段的值经过计算后再过滤的场景, 例如:
json
里面的内容包含经纬度, 我们需要对经纬度进行地理信息空间查询过滤.
a,b,c,d
分别代表语、数、英、科的分数, 查询总分等于或范围时, 需要计算后再搜索.

挑战:
大多数数据库无法使用表达式索引, 只能全表扫描, 逐条计算. 效率低下.

PG解决方案:
支持表达式索引(也可以叫函数索引), 性能指数级提升.
支持表达式统计信息柱状图, 用于优化器计算

例子:

create table a (id int, info jsonb);    

   

create index idx_a on a using gist (ST_SetSRID(ST_MakePoint((info ->> 'lon')::numeric, (info ->> 'lat')::numeric),4326));    

   

explain select * from a order by ST_SetSRID(ST_MakePoint((info ->> 'lon')::numeric, (info ->> 'lat')::numeric),4326) <->    

ST_SetSRID(ST_MakePoint(120,70),4326) limit 10;    

   

Limit (cost=0.14..0.69 rows=10 width=44)  

  -> Index Scan using idx_a on a (cost=0.14..69.40 rows=1270 width=44)  

        Order By: (st_setsrid(st_makepoint((((info ->> 'lon'::text))::numeric)::double precision, (((info ->> 'lat'::text))::numeric)::double precision), 4326) <-> '0101000020E61000000000000000005E400000000000805140'::geometry)    

create table t (id int, a float4, b float4, c float4, d float4);    

   

create index idx_t on t ((a+b+c+d));    

   

explain select * from t where a+b+c+d=400;    

   

Index Scan using idx_t on t  (cost=0.15..7.99 rows=8 width=20)    

  Index Cond: ((((a + b) + c) + d) = '400'::double precision)    

postgres=# select * from pg_stats where tablename='idx_t';

-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  

schemaname             | public  

tablename              | idx_t  

attname                | expr  

inherited              | f  

null_frac              | 0

avg_width              | 4  

n_distinct             | -0.9994  

most_common_vals       | {151.73206,180.91998,197.2688,200.11456,204.47366,223.13992}  

most_common_freqs      | {0.0002,0.0002,0.0002,0.0002,0.0002,0.0002}

histogram_bounds       | {25.474722,71.74933,85.48342,93.977295,99.26418,104.8926,110.16269,114.38039,118.075554,121.30721,124.80748,127.81897,130.67479,133.32335,136.02103,138.41626,140.85258,143.05424,145.51877,147.9408,149.95238,151.72961,153.72885,155.82372,157.6345,159.48929,161.0307,162.76514,164.57907,166.19772,167.8121,169.29343,171.28735,173.25894,174.89429,176.23984,177.65022,179.2883,180.66162,182.22772,183.88147,185.28021,186.64587,188.12837,189.66924,191.4691,192.80214,194.05939,195.64655,197.10524,198.36841,199.72656,201.35751,203.02931,204.50558,205.91415,207.49933,209.28078,210.977,212.39197,214.18248,215.5002,217.03229,218.55179,220.12622,221.61935,223.03786,224.73047,226.53156,228.12646,229.62404,231.14334,232.95035,234.51816,236.07428,237.84808,239.52545,241.77795,243.91528,246.18135,248.33812,250.06604,252.14948,254.52863,257.24,260.0845,262.6031,265.53894,268.5458,271.4497,275.00317,278.2635,281.67947,286.42548,290.9062,295.2775,301.70978,307.6002,317.32483,328.39,375.6833}  

correlation            | -0.026684083  

most_common_elems      |  

most_common_elem_freqs |  

elem_count_histogram   |  

 

 

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
3月前
|
存储 JSON 关系型数据库
带你走进PostgreSQL的世界
带你走进PostgreSQL的世界
121 0
|
6月前
|
关系型数据库 C# 数据库
2023 年度数据库 PostgreSQL 遇到年度编程语言 C# 会发生什么样的火花?
2023 年度数据库 PostgreSQL 遇到年度编程语言 C# 会发生什么样的火花?分析这俩的发展趋势,技术生态,社区活跃度,特别是国产化信创领域的支持度?
85 1
|
存储 SQL JSON
PostgreSQL学习研究笔记(一)
PostgreSQL学习研究笔记(一)
|
存储 SQL 关系型数据库
PostgreSQL:分布式数据库简史
好多分布式数据库设计时就考虑到这个趋势,并且有自己的operator来上云,我想未来大部分的分布式数据库都会跑在云上的,这个也是趋势。
PostgreSQL:分布式数据库简史
|
SQL 算法 自动驾驶
【重新发现PostgreSQL之美】- 27 无中生有
大家好,这里是重新发现PostgreSQL之美 - 27 无中生有
|
关系型数据库 数据库 PostgreSQL
【重新发现PostgreSQL之美】- 43 快速破镜重圆
大家好,这里是重新发现PostgreSQL之美 - 43 快速破镜重圆
|
算法 关系型数据库 PostgreSQL
【重新发现PostgreSQL之美】- 22 黄帝内经
大家好,这里是重新发现PostgreSQL之美 - 22 黄帝内经
|
自然语言处理 算法 关系型数据库
【重新发现PostgreSQL之美 】- 30 打蛇打七寸
大家好,这里是重新发现PostgreSQL之美 - 30 打蛇打七寸
|
安全 Oracle 关系型数据库
【重新发现PostgreSQL之美】- 36 方世玉 安全第一
大家好,这里是重新发现PostgreSQL之美 - 36 方世玉 安全第一
|
传感器 SQL 监控
【重新发现PostgreSQL之美】- 28 旋转门
大家好,这里是重新发现PostgreSQL之美 - 28 旋转门