postgresql JSON值的模式匹配

sg2wtvxw  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(117)

运行Postgres 12.5的本地docker示例（4MB work_mem）。我正在实现this pattern来搜索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);

字符串
我有一个indexed correctly查询，它在几毫秒内就可以执行一百万行。✅

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.44秒

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();
exists查询的执行时间为500ms，情况看起来更好。这就解释了。

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

型

postgresql

1条答案

按热度按时间
fkvaft9z

fkvaft9z1#

PostgreSQL 12及以上版本的SQL/JSON

My answer you have been working off已过时。目前的Postgres版本是2015年7月的9.4。
在Postgres 12中，整个设计可以从根本上简单，在SQL/JSON路径表达式中使用正则表达式。手册：
SQL/JSON路径表达式允许使用like_regex过滤器将文本匹配到正则表达式。
也支持索引。放弃实体化视图。我们只需要你的原始表和一个索引，比如：

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路径语言需要习惯。
进一步阅读：

服务器配置

在EXPLAIN输出的这一行中，一个更基本的问题变得显而易见：
堆块：精确=14659有损=65588
lossy表示您没有足够的**work_mem**。你的设置显然很低。（对于包含数百万行的表的数据库，4 MB的默认设置太低了。）请参阅：

很有可能，您需要在服务器配置部门做更多的工作。而且你似乎在RAM上受到了限制。我看到高“读”计数，这表明冷缓存和/或缓存内存的缺乏或错误配置。
这个Postgres Wiki page可以帮助你开始。

赞(0）分享  回复(0）举报  2023-08-04
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com