程序员经验分享-hwhale

JSON 值的模式匹配

2024-05-14

运行 Postgres 12.5 的本地 docker 实例（4MBwork_mem）。我正在实施这个图案 https://dba.stackexchange.com/q/108447/3684搜索 json 中的任意字段。目标是搜索并返回 JSON 列profile迅速地。

我尝试了一个EXISTS物化视图上的子查询：

CREATE TABLE end_user (
    id varchar NOT NULL,
    environment_id varchar NOT NULL,
    profile jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT end_user_pkey PRIMARY KEY (environment_id, id)
);

CREATE INDEX end_user_environment_id_idx ON private.end_user USING btree (environment_id);
CREATE INDEX end_user_id_idx ON private.end_user USING btree (id);
CREATE INDEX end_user_profile_idx ON private.end_user USING gin (profile);

CREATE MATERIALIZED VIEW user_profiles AS
SELECT u.environment_id, u.id, j.key, j.value 
FROM  end_user u, jsonb_each_text(u.profile) j(key, value);

CREATE UNIQUE INDEX on user_profiles (environment_id, id, key); 
CREATE INDEX user_profile_trgm_idx ON user_profiles using gin (value gin_trgm_ops);

我有这个查询索引正确 https://dba.stackexchange.com/a/108598/114293以便它在几毫秒内执行超过一百万行。 ✅

select * from user_profiles 
where value ilike '%auckland%' and key = 'timezone' and environment_id = 'test';

执行时间 42ms ????

Bitmap Heap Scan on user_profiles  (cost=28935.65..62591.44 rows=9659 width=65)                  
  Recheck Cond: ((value ~~* '%auckland%'::text) AND (key = 'timezone'::text))                    
  Filter: ((environment_id)::text = 'test'::text)                                              
  ->  BitmapAnd  (cost=28935.65..28935.65 rows=9659 width=0)                                     
        ->  Bitmap Index Scan on user_profile_trgm_idx  (cost=0.00..2923.95 rows=320526 width=0) 
              Index Cond: (value ~~* '%auckland%'::text)                                         
        ->  Bitmap Index Scan on user_profiles_key_idx  (cost=0.00..26006.62 rows=994408 width=0)
              Index Cond: (key = 'timezone'::text)

但是，如果我将它与exists查询以建立如下条件：

select * users u
where 
   environment_id = 'test'
and exists (
    select 1 from user_profiles p
    where 
       value ilike '%auckland%' 
       and key = 'timezone'
       and p.id = u.id
       and environment_id = 'test'   
)

它执行速度非常慢。

执行时间 17.44Seconds ????

Nested Loop  (cost=62616.01..124606.45 rows=9658 width=1459) (actual time=19206.818..28444.491 rows=332572 loops=1)                                            
  Buffers: shared hit=952734 read=624101                                                                                                                       
  ->  HashAggregate  (cost=62615.59..62707.52 rows=9193 width=15) (actual time=19205.238..19292.998 rows=332572 loops=1)                                       
        Group Key: (p.id)::text                                                                                                                                
        Buffers: shared hit=373 read=246174                                                                                                                    
        ->  Bitmap Heap Scan on user_profiles p  (cost=28935.65..62591.44 rows=9659 width=15) (actual time=278.211..18942.629 rows=332572 loops=1)             
              Recheck Cond: ((value ~~* '%auckland%'::text) AND (key = 'timezone'::text))                                                                      
              Rows Removed by Index Recheck: 17781109                                                                                                          
              Filter: ((environment_id)::text = 'test'::text)                                                                                                
              Heap Blocks: exact=43928 lossy=197955                                                                                                            
              Buffers: shared hit=373 read=246174                                                                                                              
              ->  BitmapAnd  (cost=28935.65..28935.65 rows=9659 width=0) (actual time=272.626..272.629 rows=0 loops=1)                                         
                    Buffers: shared hit=373 read=4291                                                                                                          
                    ->  Bitmap Index Scan on user_profile_trgm_idx  (cost=0.00..2923.95 rows=320526 width=0) (actual time=177.577..177.577 rows=332572 loops=1)
                          Index Cond: (value ~~* '%auckland%'::text)                                                                                           
                          Buffers: shared hit=373 read=455                                                                                                     
                    ->  Bitmap Index Scan on user_profiles_key_idx  (cost=0.00..26006.62 rows=994408 width=0) (actual time=92.586..92.589 rows=1000000 loops=1)
                          Index Cond: (key = 'timezone'::text)                                                                                                 
                          Buffers: shared read=3836                                                                                                            
  ->  Index Scan using end_user_id_idx on end_user u  (cost=0.42..6.79 rows=1 width=1459) (actual time=0.027..0.027 rows=1 loops=332572)                       
        Index Cond: ((id)::text = (p.id)::text)                                                                                                                
        Filter: ((environment_id)::text = 'test'::text)                                                                                                      
        Buffers: shared hit=952361 read=377927                                                                                                                 
Planning Time: 19.002 ms                                                                                                                                       
Execution Time: 28497.570 ms

这是一种耻辱exists如果速度快的话会很方便，因为我可以在应用程序代码中动态添加更多条件，并将额外条件表示为附加条件exists条款。

顺便说一句，横向连接确实加快了速度，但我不明白为什么会有这么大的差异：

select * from users u,
lateral (
    select id from user_profiles p
    where 
        value ilike '%auckland%' 
        and key = 'timezone' 
        and environment_id = u.environment_id 
        and p.id = u.id
   ) ss
where u.environment_id = 'test';

执行时间 304ms ????

Gather  (cost=29936.07..91577.38 rows=9658 width=1474) (actual time=1100.824..15430.620 rows=332572 loops=1)                                                     
  Workers Planned: 2                                                                                                                                             
  Workers Launched: 2                                                                                                                                            
  Buffers: shared hit=1140551 read=436286                                                                                                                        
  ->  Nested Loop  (cost=28936.07..89611.58 rows=4024 width=1474) (actual time=602.490..14805.285 rows=110857 loops=3)                                           
        Buffers: shared hit=1140551 read=436286                                                                                                                  
        ->  Parallel Bitmap Heap Scan on user_profiles p  (cost=28935.65..62492.84 rows=4025 width=22) (actual time=602.078..12247.891 rows=110857 loops=3)      
              Recheck Cond: ((value ~~* '%auckland%'::text) AND (key = 'timezone'::text))                                                                        
              Rows Removed by Index Recheck: 5927036                                                                                                             
              Filter: ((environment_id)::text = 'test'::text)                                                                                                  
              Heap Blocks: exact=14659 lossy=65588                                                                                                               
              Buffers: shared hit=373 read=246174                                                                                                                
              ->  BitmapAnd  (cost=28935.65..28935.65 rows=9659 width=0) (actual time=1087.258..1087.259 rows=0 loops=1)                                         
                    Buffers: shared hit=373 read=4291                                                                                                            
                    ->  Bitmap Index Scan on user_profile_trgm_idx  (cost=0.00..2923.95 rows=320526 width=0) (actual time=853.075..853.076 rows=332572 loops=1)  
                          Index Cond: (value ~~* '%auckland%'::text)                                                                                             
                          Buffers: shared hit=373 read=455                                                                                                       
                    ->  Bitmap Index Scan on user_profiles_key_idx  (cost=0.00..26006.62 rows=994408 width=0) (actual time=231.295..231.295 rows=1000000 loops=1)
                          Index Cond: (key = 'timezone'::text)                                                                                                   
                          Buffers: shared read=3836                                                                                                              
        ->  Index Scan using end_user_id_idx on end_user u  (cost=0.42..6.74 rows=1 width=1459) (actual time=0.022..0.022 rows=1 loops=332572)                   
              Index Cond: ((id)::text = (p.id)::text)                                                                                                            
              Filter: ((environment_id)::text = 'test'::text)                                                                                                  
              Buffers: shared hit=1140178 read=190112                                                                                                            
Planning Time: 16.877 ms                                                                                                                                         
Execution Time: 15461.571 ms

渴望听到任何关于原因的想法exists子查询太慢了，我可以在这里查看任何其他选项。

根据 Erwin 的要求进行不同计数，请注意，这是用于测试负载的虚拟数据，但其相当接近生产比率

select count(distinct environment_id)  => 4 
     , count(distinct key)             => 33
     , count(distinct value)           => 15M  
from private.user_profiles;

按照 Erwin 的建议将工作内存增加到 16MB 后更新：

ALTER SYSTEM SET work_mem to '16MB'; SELECT pg_reload_conf();

存在查询的执行时间为 500 毫秒，情况看起来更好。现在解释如下。

Gather  (cost=3926.79..400754.43 rows=9658 width=1459) (actual time=312.213..9396.610 rows=332572 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1141083 read=431918
  ->  Nested Loop  (cost=2926.79..398788.63 rows=4024 width=1459) (actual time=155.271..8987.721 rows=110857 loops=3)
        Buffers: shared hit=1141083 read=431918
        ->  Parallel Bitmap Heap Scan on user_profiles p  (cost=2926.36..371669.88 rows=4025 width=15) (actual time=150.989..2962.870 rows=110857 loops=
              Recheck Cond: (value ~~* '%auckland%'::text)
              Filter: (((environment_id)::text = 'test'::text) AND (key = 'timezone'::text))
              Heap Blocks: exact=82556
              Buffers: shared hit=981 read=241730
              ->  Bitmap Index Scan on user_profile_trgm_idx  (cost=0.00..2923.95 rows=320526 width=0) (actual time=243.604..243.605 rows=332572 loops=1
                    Index Cond: (value ~~* '%auckland%'::text)
                    Buffers: shared hit=828
        ->  Index Scan using end_user_id_idx on end_user u  (cost=0.42..6.74 rows=1 width=1459) (actual time=0.054..0.054 rows=1 loops=332572)
              Index Cond: ((id)::text = (p.id)::text)
              Filter: ((environment_id)::text = 'test'::text)
              Buffers: shared hit=1140102 read=190188
Planning Time: 9.932 ms
Execution Time: 9427.067 ms

Postgres 12 或更高版本中的 SQL/JSON

我的回答是你一直在努力 https://dba.stackexchange.com/a/108598/3684已过时。当前的 Postgres 版本是 2015 年 7 月的 9.4。

在 Postgres 12 中，整个设计可以是极其简单在 SQL/JSON 路径表达式中使用正则表达式。手册： https://www.postgresql.org/docs/current/functions-json.html#JSONPATH-REGULAR-EXPRESSIONS

SQL/JSON 路径表达式允许将文本与正则表达式匹配like_regex filter.

也有索引支持。废弃物化视图。我们需要的只是您的原始表和索引，例如：

CREATE INDEX end_user_path_ops_idx ON end_user USING GIN (profile jsonb_path_ops);

此查询与您的原始查询等效，并且可以使用索引：

SELECT *
FROM   end_user u
WHERE  environment_id = 'test'
AND    profile @? '$.timezone ? (@ like_regex "auck" flag "i")';

数据库小提琴

一个缺点是 SQL/JSON 路径语言需要习惯。
进一步阅读：

  • 在 JSONB 记录数组中查找包含键的行 https://dba.stackexchange.com/a/196635/3684
  • https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH

服务器配置

在这行代码中，一个更基本的问题变得显而易见EXPLAIN output:

堆块：精确=14659 有损=65588

lossy表明你没有足够的钱work_mem https://www.postgresql.org/docs/current/runtime-config-resource.html。你的设置显然很低。 （对于涉及数百万行表的数据库，默认设置 4 MB 太低了。）请参阅：

  • 带有位图索引扫描的查询计划中的“Recheck Cond:”行 https://dba.stackexchange.com/a/106267/3684
  • PostgreSQL GIN 索引比 pg_trgm 的 GIST 慢？ https://stackoverflow.com/questions/43008382/postgresql-gin-index-slower-than-gist-for-pg-trgm/44853236#44853236

很可能，您需要在服务器配置部门做更多的事情。一般来说，你的内存似乎受到限制。我看到高“读取”计数，这表明缓存冷和/或缓存内存缺乏或配置错误。

This Postgres 维基页面 https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server可以帮助您入门。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

postgresql

databasedesign

exists

JsonPath

postgresqlperformance

JSON 值的模式匹配 的相关文章

  • 如何在Django项目中使用PostgreSQL的存储过程或函数

    我正在开发一个 Django 项目 我决定在 PostgreSQL 中编写逻辑代码 而不是用 Python 编写 因此 我在 PostgreSQL 中创建了一个存储过程 例如 存储过程如下所示 create or replace proce

  • 有哪些可用选项可以识别和删除 Postgres 中的无效对象（例如：损坏的索引）

    有哪些可用选项可以识别和删除 Postgres 中的无效对象 如果您指的是检测 无效 创建不良 索引 显然 Postgres 在尝试创建索引时可能会 失败 然后查询规划器将不会使用它们 尽管它们存在于您的系统中 此查询将检测 失败 索引 h

  • 如何加速spark df.write jdbc到postgres数据库？

    我是 Spark 新手 正在尝试使用 df write 加速将数据帧的内容 可以有 200k 到 2M 行 附加到 postgres 数据库 df write format jdbc options url psql url spark d

  • PostgreSQL - 致命：用户“myuser”身份验证失败[关闭]

    Closed 这个问题是无关 help closed questions 目前不接受答案 我刚刚在我的 Ubuntu Box 中安装了 PostGreSQL 我想做的第一件事就是创建一个数据库 我读了文档 http www postgres

  • PostgreSQL 错误 42501：架构权限被拒绝

    我正在 ASP NET 中构建一个用户注册系统 使用 PostgreSQL 数据库来维护用户信息 作为注册过程的一部分 用户会收到一条确认消息 他们必须单击其中的链接来验证其电子邮件地址 然后 他们将进入一个可以创建密码的页面 一旦用户提供

  • SELECT 在 PL/pgSQL 函数中引发异常

    我想在函数内实现循环 但收到此错误 ERROR 查询没有结果数据的目标 代码 CREATE OR REPLACE FUNCTION my function ill int ndx bigint RETURNS int AS DECLARE

  • 将 SQL Server 2008 DB 迁移到 Postgres [重复]

    这个问题在这里已经有答案了 我想将 SQL Server 2008 数据库迁移到 Postgres 有没有一种无痛的方法来做到这一点 是否有任何工具可以扫描架构和存储过程以标记兼容性问题 无痛http dbconvert com conve

  • “WHERE”处或附近的语法错误

    创建 postgres 函数时会导致错误 如下所示 错误 WHERE 处或附近的语法错误 第 19 行 其中 s shift id shiftid 错误 错误 WHERE 处或附近的语法错误 SQL状态 42601 人物 108 请帮忙 C

  • 如何修复“缺少表的 FROM 子句条目”错误

    我正在尝试根据游戏 ID 获取平台名称 我有如下三个表 我正在尝试加入它们以获得所需的结果 Games Id 1 2 3 4 Game Platforms Id game id platform id 1 1 1 2 1 2 3 3 3

  • PostgreSQL 和锁定

    希望一些比我更聪明的 DBA 可以帮助我找到一个好的解决方案来完成我需要做的事情 为了便于讨论 我们假设我有一个名为 work 的表 其中包含一些列 其中一列表示给定客户端对该行工作的所有权 场景是 我将连接 2 个客户端并轮询表以查找要完

  • 使用所有连接的 Flask unittest 和 sqlalchemy

    在进行了大约 100 个单元测试后 我刚刚在 Flask 应用程序上运行单元测试时遇到了问题 所有单元测试都会通过 但是当一次全部运行时 它们将失败并出现以下错误 OperationalError OperationalError FATA

  • 使用 psycopg2 转义 Postgres 的 SQL“LIKE”值

    psycopg2 是否有转义 a 值的函数LIKEPostgres 的操作数 例如 我可能想匹配以字符串 20 of all 开头的字符串 所以我想写这样的内容 sql WHERE LIKE myvalue s cursor fetchal

  • 为什么要为字符变化类型指定长度

    参考 Postgres 文档字符类型 http www postgresql org docs current static datatype character html 我不清楚指定字符变化 varchar 类型的长度 假设 字符串的长

  • 续集打字稿多对多关系模型数据

    我正在使用sequelize续集打字稿 https github com RobinBuschmann sequelize typescript图书馆 并试图实现以下关系 Team ts Scopes withPlayers include

  • PostgreSQL WHERE 计数条件

    我在 PostgreSQL 中有以下查询 SELECT COUNT a log id AS overall count FROM Log as a License as b WHERE a license id 7 AND a licens

  • 如何在 Postgresql 中将 GIST 或 GIN 索引与 hstore 列一起使用？

    我正在使用 postgresql 9 3 的 hstore 我正在尝试对 hstore 列使用索引就像文档所述 http www postgresql org docs 9 3 static hstore html 我的问题是索引似乎没有被

  • sql直接获取表行数的方法

    stackoverflow 的朋友们大家好 我的例行程序中有一个我认为不必要的步骤 假设您想从图库中获取所有图像 并限制每页一定数量的图像 db PDO object start pagenum x images per page limi

  • 将 Gson 与路径一起使用

    使用简单的 Json 文件 例如 menu id file value File popup menuitem value New onclick CreateNewDoc value Open onclick OpenDoc value

  • Postgres LEFT JOIN 与 WHERE 条件

    我需要使用 where 条件左连接两个表 Table time table id rid start date end date 1 2 2017 07 01 00 00 00 2018 11 01 00 00 00 2 5 2017 01

  • Postgres 性能问题

    我们正在运行 Postgres 9 1 3 最近我们的一台服务器开始遇到重大性能问题 我们的查询在一段时间内运行良好 但截至 8 月 1 日 速度显着减慢 看起来大多数有问题的查询都是 Select 查询 带有 count 的查询尤其糟糕

随机推荐

  • 学习实体框架[关闭]

    就目前情况而言 这个问题不太适合我们的问答形式 我们希望答案得到事实 参考资料或专业知识的支持 但这个问题可能会引发辩论 争论 民意调查或扩展讨论 如果您觉得这个问题可以改进并可能重新开放 访问帮助中心 help reopen questi

  • 使用全文搜索查找精确匹配

    使用 Sql Server 2008 如何使用全文搜索来实际找到精确的字符串匹配 我对此感到非常困难 而且我在网上找不到令人满意的解决方案 例如 如果我正在搜索字符串 Bojan Skrchevski 我希望第一个结果正是如此 到目前为止

  • 计算数字的二进制表示形式中 1 的数量的最佳方法。 (MIPS)

    我需要计算二进制数中 1 的数量 比如说 5 所以 00001001 将是 2 或 n 2 我正在使用 MIPS 最好的方法来做到这一点 最好的方法是count them 您可以检查是否设置了最低有效位 a1 by and用一个来代替它 如

  • 在 Firebase 中为 TextView Swift 保存字体和大小的方法是什么

    我想在 Firebase 中保存 Swift 中 TextView 的字体 大小和对齐方式 这样我就可以在另一个视图中调用它 我只能将颜色保存在 Firebase 中 这是显示我是如何做到的的代码 IBAction func SendBtn

  • 如何在发送邮件之前验证 smtp 凭据？

    我需要验证在中设置的用户名和密码SmtpClient发送邮件之前的实例 使用此代码 SmtpClient client new SmtpClient host client Credentials new NetworkCredential

  • 如何删除或更改默认帮助命令？

    如何删除或至少更改discord py 中默认帮助命令的格式 我认为改变格式会很好 我根本不喜欢这种格式 尝试这个 bot remove command help 在导入之后将其放在代码的顶部 然后创建你自己的 或者要格式化它 请检查一下

  • 升级到最新支持库后Android JACK编译器错误

    Android Studio 2 2 3 Windows 10 64位 构建工具版本 25 Android Gradle插件版本2 2 3 升级到最新的支持库 从 23 4 0 到 25 1 0 并更改编译版本 从 23 到 25 后 我收

  • Groupby 应用自定义函数 Pandas

    我正在尝试在 pandas 中应用类似于 dplyr 中的 groupby 和 mutate 功能的自定义函数 我想做的是给出这样的 pandas 数据框 df pd DataFrame category1 a a a b b b cate

  • 表已满（使用 MEMORY 引擎）

    我想将生产数据库传输到我的开发机器上进行测试 它有 6 张桌子MEMORY出于性能目的的引擎 I did mysqldump routines hxxx uxxx pxxx prod database gt prod dump sql 当我

  • 在Python中根据for循环中的字典键创建动态变量

    我有这个程序 dict1 x 1 y 10 20 for each in list dict1 keys exec each dict1 each exec x dict x exec y dict y print x print y 我真

  • 寻找将集合映射到整数的双射函数

    对于任意两个序列 a b 其中 a a1 a2 an 且 b b1 b2 bn 0a b具有相同的元素 而不关心它们的顺序 例如 如果 a 1 1 2 3 b 2 1 3 1 c 3 2 1 3 则 f a f b f a f b 我知道有

  • 如何在 Tableau 中将数据规范化为某个范围

    在 Tableau 中 我尝试实现以下规范化逻辑 https stats stackexchange com questions 70801 how to normalize data to 0 1 range https stats st

  • MySQL 获取时间优化

    o我有一个包含 200 万个寄存器的表 但它很快就会增长得更多 基本上 该表包含具有相应描述符的图像的兴趣点 当我尝试执行选择在空间上靠近查询点的点的查询时 总执行时间花费太长 更准确地说 持续时间 获取 0 484 秒 27 441 秒

  • gnuplot：字体较小的字幕

    有人知道如何在 gnuplot 中插入较小字体的字幕吗 目前 我创建字幕的方式是使用 n在标题中 另外 我希望字幕的字体较小 提前致谢 这至少适用于 postscript 终端 出于某种原因x11不想缩放我的字体 也许这是一个错误 set

  • 枚举的子类化

    有没有一种简单的方法来子类化Javaenum 我问这个问题是因为我有大约 10 个实现相同接口的对象 但它们对某些方法也有相同的实现 因此我想通过将所有相同的实现放置在扩展的中间对象中来重用代码Enum它也是我需要的所有其他类的超类 或许事

  • 如何检测Windows版本是否合法？ [关闭]

    Closed 这个问题不符合堆栈溢出指南 help closed questions 目前不接受答案 我对获取版本信息不感兴趣 我想做的就是确保我的应用程序只能在合法版本的 Windows 上运行 而不是在盗版版本上运行 Windows 使

  • Django重复输入错误（1062）的原因？

    我更新了下面的信息以引用给我相同错误的不同模型 视图 它是一个更简单的模型 因此需要考虑的变量更少 我有以下模型 class Imaging order Order order description models ForeignKey I

  • Windows Phone SDK8 安装疑难解答

    我在运行 Windows 8 非专业版 的 PC 上使用 Visual Studio 2012 SDK 8 0 我决定购买另一个硬盘在同一台 PC 上安装 Windows Pro 以便在 PC 上运行 Windows Phone 模拟器 P

  • 在 TypeScript 中将 Chai 自定义插件声明为 NodeJS 全局变量

    这是我之前的问题 https stackoverflow com questions 61676032 declare nodejs global variables in before hook in webdriverio using

  • JSON 值的模式匹配

    运行 Postgres 12 5 的本地 docker 实例 4MBwork mem 我正在实施这个图案 https dba stackexchange com q 108447 3684搜索 json 中的任意字段 目标是搜索并返回 JS

热门标签