AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

PgSQL · 应用案例 · PostgreSQL 图像搜索实践

2018-09-22 2027
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
简介： 背景imgsmlr是PostgreSQL的一款支持以图搜图的插件， 支持1、几种图像特征值数据类型，2、图像特征值相似算子，3、图像特征值相似排序索引支持，4、图像相似排序的索引（通过扩展GiST索引接口实现）支持，5、png,gif等图像格式特征值提取函数。

背景

imgsmlr是PostgreSQL的一款支持以图搜图的插件， 支持

1、几种图像特征值数据类型，

2、图像特征值相似算子，

3、图像特征值相似排序索引支持，

4、图像相似排序的索引（通过扩展GiST索引接口实现）支持，

5、png,gif等图像格式特征值提取函数。

图像特征值为64*64的16个区域经过小波转换后的16个浮点数。

在数据量（图片数）非常庞大时，输入一个图片特征值，搜索相似度排行前N的图片，性能如何呢？如何优化呢？

接下来的3篇文档将分别介绍如下三种场景的图像特征值搜索性能以及优化思路：

1、单机单表

2、单机分区表（使用DBLINK 异步调用）

https://github.com/digoal/blog/blob/master/201809/20180904_03.md

3、citus，多机，sharding 表

https://github.com/digoal/blog/blob/master/201809/20180904_04.md

阿里云postgresql支持imgsmlr插件

1、在需要使用图像搜索的数据库中创建插件

create extension imgsmlr;

单节点 单表图像搜索 (4亿图像)

1、创建生成随机图像特征值signature的UDF。

create or replace function gen_rand_img_sig(int) returns signature as $$  
  select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;  
$$ language sql strict;  

postgres=# select * from gen_rand_img_sig(10);  
                                                                         gen_rand_img_sig                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 (6.744310, 5.105020, 0.087113, 3.808010, 8.129480, 2.834540, 2.495250, 0.940481, 0.033208, 6.583490, 2.840330, 1.422440, 6.683830, 0.080847, 8.327730, 2.471430)  
(1 row)  
  
postgres=# select * from gen_rand_img_sig(10);  
                                                                         gen_rand_img_sig                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 (3.013650, 6.170690, 0.601905, 2.692030, 1.268540, 7.803740, 9.757770, 5.537750, 0.391753, 4.440790, 1.201580, 5.501380, 6.166980, 0.240686, 9.768680, 2.911290)  
(1 row)

2、建表，建图像特征值索引

create table t_img_sig (id int primary key, sig signature);  
create index idx_t_img_sig_1 on t_img_sig using gist(sig);

3、写入约4亿随机图像特征值

vi testsig.sql  
\set id random(1,2000000000)  
insert into t_img_sig values (:id, gen_rand_img_sig(10)) on conflict(id) do nothing;  

pgbench -M prepared -n -r -P 1 -f ./testsig.sql -c 32 -j 32 -t 20000000  

postgres=# select * from t_img  limit 10;  
    id     |                                                                               sig                                                                                  
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  47902935 | (5.861920, 1.062770, 8.318020, 2.205840, 0.202951, 6.956610, 1.413190, 2.898480, 8.961630, 6.377800, 1.110450, 6.684520, 2.286290, 7.850760, 1.832650, 0.074348)  
 174656795 | (2.165030, 0.183753, 9.913950, 9.208260, 5.165660, 6.603510, 2.008380, 8.117910, 2.358590, 5.466330, 9.139280, 8.893700, 4.664190, 9.361670, 9.016990, 2.271000)  
  96186891 | (9.605980, 4.395920, 4.336720, 3.174360, 8.706960, 0.155107, 9.408940, 4.531100, 2.783530, 5.681780, 9.792380, 6.428320, 2.983760, 9.733290, 7.635160, 7.035780)  
  55061667 | (7.567960, 5.874530, 5.222040, 5.638520, 3.488960, 8.770750, 7.054610, 7.239630, 9.202280, 9.465020, 4.079080, 5.729770, 0.475227, 8.434800, 6.873730, 5.140080)  
  64659434 | (4.860650, 3.984440, 3.009900, 5.116680, 6.489150, 4.224800, 0.609752, 8.731120, 6.577390, 8.542540, 9.096120, 8.976700, 8.936000, 2.836270, 7.186250, 6.264300)  
  87143098 | (4.801570, 7.870150, 0.939599, 3.666670, 1.102340, 5.819580, 6.511330, 6.430760, 0.584531, 3.024190, 6.255460, 8.823820, 5.076960, 0.181344, 8.137380, 1.230360)  
 109245945 | (7.541850, 7.201460, 6.858400, 2.605210, 1.283090, 7.525200, 4.213240, 8.413760, 9.707390, 1.916970, 1.719320, 1.255280, 9.006780, 4.851420, 2.168250, 5.997360)  
   4979218 | (8.463000, 4.051410, 9.057320, 1.367980, 3.344340, 7.032640, 8.583770, 1.873090, 5.524810, 0.187254, 5.783270, 6.141040, 2.479410, 6.406450, 9.371700, 0.050690)  
  72846137 | (7.018560, 4.039150, 9.114800, 2.911170, 5.531180, 8.557330, 6.739050, 0.103649, 3.691390, 7.584640, 8.184180, 0.599390, 9.037130, 4.090610, 4.369770, 6.480000)  
  36813995 | (4.643480, 8.704640, 1.073880, 2.665530, 3.298300, 9.244280, 5.768050, 0.887555, 5.990350, 2.991390, 6.186550, 6.464940, 6.187140, 0.150242, 2.123070, 2.932270)  
(10 rows)  
  
Time: 58.101 ms

写入约4.39亿图像特征值。

postgres=# select count(*) from t_img_sig;  
   count     
-----------  
 438924137  
(1 row)

4、输入一个图像特征值，搜索与之最相似的图像。

explain (analyze,verbose,timing,costs,buffers) select * from t_img_sig order by sig <-> '(5.07998,6.80827,5.42024,2.53619,4.10843,0.532198,4.33886,9.60262,6.68369,8.01305,9.60298,8.087,1.25819,6.54424,6.04902,5.3444)' limit 1;  

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_img_sig where signature_distance(sig,'(5.07998,6.80827,5.42024,2.53619,4.10843,0.532198,4.33886,9.60262,6.68369,8.01305,9.60298,8.087,1.25819,6.54424,6.04902,5.3444)') > 0.9 order by sig <-> '(5.07998,6.80827,5.42024,2.53619,4.10843,0.532198,4.33886,9.60262,6.68369,8.01305,9.60298,8.087,1.25819,6.54424,6.04902,5.3444)' limit 1;  
                                                                                                                          QUERY PLAN                                                                                                          
                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.48..0.51 rows=1 width=72) (actual time=4094.810..4094.812 rows=1 loops=1)  
   Output: id, sig, ((sig <-> '(5.079980, 6.808270, 5.420240, 2.536190, 4.108430, 0.532198, 4.338860, 9.602620, 6.683690, 8.013050, 9.602980, 8.087000, 1.258190, 6.544240, 6.049020, 5.344400)'::signature))  
   Buffers: shared hit=205999  
   ->  Index Scan using idx_t_img_sig_1 on public.t_img_sig  (cost=0.48..5361351.06 rows=146395778 width=72) (actual time=4094.808..4094.808 rows=1 loops=1)  
         Output: id, sig, (sig <-> '(5.079980, 6.808270, 5.420240, 2.536190, 4.108430, 0.532198, 4.338860, 9.602620, 6.683690, 8.013050, 9.602980, 8.087000, 1.258190, 6.544240, 6.049020, 5.344400)'::signature)  
         Order By: (t_img_sig.sig <-> '(5.079980, 6.808270, 5.420240, 2.536190, 4.108430, 0.532198, 4.338860, 9.602620, 6.683690, 8.013050, 9.602980, 8.087000, 1.258190, 6.544240, 6.049020, 5.344400)'::signature)  
         Filter: (signature_distance(t_img_sig.sig, '(5.079980, 6.808270, 5.420240, 2.536190, 4.108430, 0.532198, 4.338860, 9.602620, 6.683690, 8.013050, 9.602980, 8.087000, 1.258190, 6.544240, 6.049020, 5.344400)'::signature) > '0.9'::double precision)  
         Buffers: shared hit=205999  
 Planning Time: 0.073 ms  
 Execution Time: 4194.485 ms  
(10 rows)

性能与瓶颈

性能：4.39亿图像特征值，以图搜图约4.2秒。

瓶颈:

1、扫描了大量的索引页（205999）。

优化思路

1、压缩精度，比如使用3位小数。据用户说有10倍性能提升。

精度优化如下，使用新的生成图像特征值的函数，使用3位小数。

create or replace function gen_rand_img_sig3(int) returns signature as $$  
  select ('('||rtrim(ltrim(array(select trunc((random()*$1)::numeric,3) from generate_series(1,16))::text,'{'),'}')||')')::signature;  
$$ language sql strict;

例子如下

postgres=# select gen_rand_img_sig3(10);  
                                                                        gen_rand_img_sig3                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 (2.984000, 3.323000, 4.083000, 6.292000, 5.008000, 9.029000, 6.208000, 1.141000, 1.796000, 9.257000, 1.397000, 1.235000, 7.157000, 3.745000, 0.112000, 7.723000)  
(1 row)

2、使用分区表+dblink异步接口并行调用。（内核层面直接支持imgsmlr gist index scan并行更好）

下一篇介绍

3、使用citus sharding。多机，提高整体计算能力。 （因为扫描大量索引页，即使CPU没有瓶颈，将来内存带宽也会成为瓶颈。多机可以解决这个问题。）

下一篇介绍

4、内核层面，支持维度更低的signature，现在是16片，比如支持降低到4片，性能也可以提升。

精度现象

1、当有记录可以完全匹配时，扫描少量INDEX PAGE。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from  t_img_sig order by sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179000)'::signature limit 1;
                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.48..0.49 rows=1 width=72) (actual time=1.596..1.598 rows=1 loops=1)
   Output: id, sig, ((sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179000)'::signature))
   Buffers: shared hit=125
   ->  Index Scan using t_img_sig1_sig_idx on public.t_img_sig  (cost=0.48..7318159.22 rows=785457848 width=72) (actual time=1.594..1.595 rows=1 loops=1)
         Output: id, sig, (sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179000)'::signature)
         Order By: (t_img_sig.sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179000)'::signature)
         Buffers: shared hit=125
 Planning Time: 0.072 ms
 Execution Time: 1.621 ms
(9 rows)

2、当修改少量内容，少量值完全匹配，其他值不完全匹配时，扫描的INDEX PAGE增加。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from  t_img_sig order by sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179001)'::signature limit 1;
                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.48..0.49 rows=1 width=72) (actual time=7.051..7.052 rows=1 loops=1)
   Output: id, sig, ((sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179001)'::signature))
   Buffers: shared hit=454
   ->  Index Scan using t_img_sig1_sig_idx on public.t_img_sig  (cost=0.48..7324626.56 rows=786152016 width=72) (actual time=7.049..7.049 rows=1 loops=1)
         Output: id, sig, (sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179001)'::signature)
         Order By: (t_img_sig.sig <-> '(3.727000, 2.594000, 0.185000, 3.996000, 6.450000, 7.126000, 5.499000, 1.540000, 8.239000, 6.262000, 2.053000, 2.566000, 4.522000, 6.929000, 1.582000, 2.179001)'::signature)
         Buffers: shared hit=454
 Planning Time: 0.074 ms
 Execution Time: 7.076 ms
(9 rows)

3、当大量修改值，不能完全匹配时，需要扫描大量INDEX PAGE。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from  t_img_sig order by sig <-> '(7.727000, 3.594000, 1.185000, 4.996000, 6.950000, 7.129000, 5.429000, 1.520000, 8.219000, 6.222000, 2.013000, 2.536000, 4.532000, 6.939000, 1.538000, 2.178000)'::signature limit 1;
                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.47..0.48 rows=1 width=72) (actual time=2528.890..2528.891 rows=1 loops=1)
   Output: id, sig, ((sig <-> '(7.727000, 3.594000, 1.185000, 4.996000, 6.950000, 7.129000, 5.429000, 1.520000, 8.219000, 6.222000, 2.013000, 2.536000, 4.532000, 6.939000, 1.538000, 2.178000)'::signature))
   Buffers: shared hit=121510
   ->  Index Scan using t_img_sig1_sig_idx on public.t_img_sig  (cost=0.47..1361409.21 rows=146121007 width=72) (actual time=2528.887..2528.888 rows=1 loops=1)
         Output: id, sig, (sig <-> '(7.727000, 3.594000, 1.185000, 4.996000, 6.950000, 7.129000, 5.429000, 1.520000, 8.219000, 6.222000, 2.013000, 2.536000, 4.532000, 6.939000, 1.538000, 2.178000)'::signature)
         Order By: (t_img_sig.sig <-> '(7.727000, 3.594000, 1.185000, 4.996000, 6.950000, 7.129000, 5.429000, 1.520000, 8.219000, 6.222000, 2.013000, 2.536000, 4.532000, 6.939000, 1.538000, 2.178000)'::signature)
         Buffers: shared hit=121510
 Planning Time: 0.092 ms
 Execution Time: 2582.558 ms
(9 rows)

具体原因可以参考

https://github.com/postgrespro/imgsmlr/blob/master/imgsmlr_idx.c

https://www.postgresql.org/docs/devel/static/xindex.html

参考

https://github.com/postgrespro/imgsmlr

《PostgreSQL 相似搜索插件介绍大汇总 (rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

文章标签：
图像搜索
云原生数据库 PolarDB
索引
关系型数据库
PostgreSQL
关键词：
pgsql PostgreSQL
PostgreSQL实践
PostgreSQL案例
PostgreSQL应用
云数据库 RDS PostgreSQL 版pgsql
db匠
目录
相关文章
开发者小助理
|
SQL 关系型数据库 数据挖掘
PolarDB for PostgreSQL 文档大全｜阿里云产品内容精选（四十）
本文内容选自阿里云开发者社区数据库版块。
开发者小助理
270 0
db匠
|
SQL 关系型数据库 测试技术
PgSQL · 应用案例 · 阿里云 RDS PostgreSQL 高并发特性 vs 社区版本
背景 进程模型数据库，需要为每个会话指派独立的进程与之服务，在连接数非常多，且大都是活跃连接时，进程调度浪费或引入的开销甚至远远大于实际任务需要的开销（例如上下文切换，MEMCPY等），性能下降会较为严重。
db匠
2927 0
萌萌怪兽
|
新零售 人工智能 测试技术
PostgreSQL技术周刊第4期：大家关注的PostgreSQL近期活动列表整理出来啦！
PostgreSQL（简称PG）的开发者们：云栖社区已有5000位PG开发者，发布了3000+PG文章（文章列表），沉淀了700+的PG精品问答（问答列表）。 PostgreSQL技术周刊会为大家介绍最新的PG技术与动态、预告活动、最热问答、直播教程等，欢迎大家订阅PostgreSQL技术周刊。
萌萌怪兽
2982 0
db匠
|
关系型数据库 测试技术 数据库
PgSQL · 案例分享 · PostgreSQL 性能诊断指南
背景 数据库的性能优化是一个非常经典的话题，数据库的优化手段以及优化的角度也各不相同。 例如，可以从OS内核、网络、块设备、编译器、文件系统、SQL、数据库参数、业务逻辑、源码等各个方面去进行优化。 但是如果在优化前了解瓶颈在什么地方，可以向打鼹鼠一样，先打大的，起到事半功倍的效果。 本文将针对Linux平台下的PostgreSQL服务，讲解如何诊断PostgreSQL 数据库服务的瓶颈，了
db匠
4803 0
德哥
|
SQL 存储 算法
阿里云PostgreSQL案例精选2
### 标签 PostgreSQL , 阿里云
德哥
1846 0
db匠
|
SQL 关系型数据库 PostgreSQL
PgSQL · 应用案例 · 惊天性能！单RDS PostgreSQL实例支撑 2000亿
背景 20亿用户，每个用户1000个标签，基于任意标签组合圈选、透视（业务上的需求是一次最多计算100个标签的组合）。 相当于要处理2000亿记录。 1、实时求标签组合的记录数。（即满足标签组合的用户有多少） 2、用户ID。
db匠
2292 0
技术小能手
|
SQL 关系型数据库 PostgreSQL
PgSQL · 应用案例 · 惊天性能！单RDS PostgreSQL实例支撑 2000亿
技术小能手
4880 0
db匠
|
SQL 关系型数据库 调度
PgSQL · 答疑解惑 · PostgreSQL 9.6 并行查询实现分析
背景 随着PG9.5 项目的release，属于PG9.6的代码也陆续进入代码主干，其中最让人激动的特性并行查询终于进入了核心代码。pger们对这个新特性期待了太久的时间,代码刚提交我们就迫不及待的拿到，从设计到性能进行一番探究，并通过本文介绍给大家。 并行技术的过去和未来 这是个很困难的工作
db匠
3002 0
萌萌怪兽
|
关系型数据库 数据库 索引
PostgreSQL技术周刊第15期：PostgreSQL年度重磅干货合辑，百份资源与你分享
PostgreSQL（简称PG）的开发者们：云栖社区已有5000位PG开发者，发布了3000+PG文章（文章列表），沉淀了700+的PG精品问答（问答列表）。 PostgreSQL技术周刊会为大家介绍最新的PG技术与动态、预告活动、最热问答、直播教程等，欢迎大家订阅PostgreSQL技术周刊。
萌萌怪兽
4343 0
yq传送门
Oracle应用迁移到AnalyticDB for PostgreSQL指导 | 6月17号云栖夜读
今天的首篇文章，讲述了：AnalyticDB for PostgreSQL（简称：ADB for PG）对Oracle语法有着较好的兼容，本文介绍如何将Oracle应用迁移到AnalyticDB for PostgreSQL。
yq传送门
6568 0
数据库

关系型数据库

热门文章

最新文章

  • 1
    什么场景应该用 MongoDB ？
  • 2
    PostgreSQL 如何实现批量更新、删除、插入
  • 3
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 4
    PostgreSQL 9种索引的原理和应用场景
  • 5
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 6
    Jedis常见异常汇总
  • 7
    PostgreSQL 触发器用法详解 - 1
  • 8
    时序数据库分析 - TimescaleDB时序数据库介绍
  • 9
    PostgreSQL 百亿地理位置数据 近邻查询性能
  • 10
    page fault带来的性能问题
  • 1
    分库分表—2.详细介绍一
    15
  • 2
    RDS用多了，你还知道MySQL主从复制底层原理和实现方案吗？
    17
  • 3
    分库分表—1.简要概述
    100
  • 4
    MySQL底层概述—10.InnoDB锁机制
    50
  • 5
    MySQL底层概述—9.ACID与事务
    39
  • 6
    MySQL底层概述—8.JOIN排序索引优化
    55
  • 7
    MySQL底层概述—7.优化原则及慢查询
    76
  • 8
    MySQL底层概述—6.索引原理
    51
  • 9
    MySQL底层概述—5.InnoDB参数优化
    45
  • 10
    MySQL底层概述—4.InnoDB数据文件
    24

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情
  • 云数据库 RDS PostgreSQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PostgreSQL实战进阶
  • PolarDB for PostgreSQL 内核解读系列课程
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • 云原生数据库PolarDB 快速入门

    • 相关电子书

    更多
  • PostgreSQL在哈啰的实践-周飞
  • 阿里云RDS PostgreSQL实践课 2 实时用户画像数据库实践
  • PolarDB for PostgreSQL 开源必读手册

    • 相关实验场景

    更多
  • 数据库实验室挑战任务-高级任务
  • 数据库实验室挑战任务-中级任务
  • 数据库实验室挑战任务-初级任务
  • 阿里云数据库产品实验室
  • 如何开通PostgreSQL的专属集群
  • 快速上手并跑通AnalyticDB PostgreSQL版TPC-H测试

    • 推荐镜像

    更多
  • postgresql
  • mysql
  • mongodb
    • 下一篇
    阿里云oss简介和如何对接使用