PostgreSQL子查询和JSON导致查询速度变慢--一条SQL引起的惨案

砂丁
665 阅读3分钟

PostgreSQL子查询和JSON导致查询速度变慢--一条SQL引起的惨案

在写PostgreSQL的时候遇到一个事情，一条SQL语句查询时间要2000ms-3000ms！！！并且排查数据库监控工具发现这个SQL还被一直调用，不禁心里一凉，params不是使用了索引吗，这样下去PostgreSQL可能会炸啊。所以马上拉出这个SQL来看一下。

 select exists(
     select 1 from library where delete_status=false and library.params @? ('($.catalog[*] ? (@.name == "'|| a.value ||'"))') :: jsonpath
 ) from jsonb_array_elements_text('["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]'::jsonb) a;

使用了JSONB和JSON PATH以及JSONB处理函数，有点复杂啊。。。但是没有关系，作者刚刚搞清楚了PostgreSQL中JSON的用法，这篇文章讲了JSON索引，JSON使用可以看www.postgres.cn/docs/14/dat…和www.postgres.cn/docs/14/fun…

SQL语句解释

这里先解释一下上面SQL，数据表结构是：

 create table library(id bigserial primary key ,
     params jsonb,
     delete_status bool
 );
 create index library_params_gin on library using gin(params);
 params格式是{"catalog": [{"name": "KftNvvdQgn"}, {"name": "kcNwEkPmJy"}]}

library.params @? ('($.catalog[*] ? (@.name == "'|| a.value ||'"))') 是一个JSON PATH的查询语句

  1. @?是针对library.params进行后面的帅选表达式
  1. $是表示要查询JSON文本的变量，[*]表示获取数组下的所有元素，$.catalog.[*]等价于获取library.params.catalog数组下的所有元素
  1. @是帅选表达式中表示当前路径元素的变量，@.name是等价于library.params.catalog[*].name
  2. "'|| a.value ||'"是一个PostgreSQL字符串的拼接语句，||是拼接操作符，假设a.value为‘aaa’等价于@.name == "aaa"

jsonb_array_elements_text('["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]'::jsonb) a是一个JSONB处理函数

jsonb_array_elements_text是将JSON数组展开成一组文本值格式如下：

    value
 -----------
  PcYAxtOBpt
  jjYgoXiRcK
  aaa

串联起来，这个SQL语句的意思就是找出library.params.catalog这个数组中所有JSON元素的name属性，是否等于["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]数组中的值

查询分析

分析查询应该使用explain analyze关键字，我们里运行一下

 explain analyze select exists(
     select 1 from library where delete_status=false and
                             library.params @?? ('($.catalog[*] ? (@.name == "'|| a.value ||'"))') :: jsonpath
 ) from jsonb_array_elements_text('["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]'::jsonb) a;

运行结构如下，可以看到没有走索引并且执行时间是惊人的7000ms(测试数据库有100w记录，实际要少一半)：

 Function Scan on jsonb_array_elements_text a  (cost=0.00..620.60 rows=100 width=1) (actual time=2454.358..7244.266 rows=3 loops=1)
   SubPlan 1
     ->  Seq Scan on library  (cost=0.00..60343.00 rows=9739 width=0) (actual time=2414.746..2414.746 rows=1 loops=3)
 "          Filter: ((NOT delete_status) AND (params @? ((('($.catalog[*] ? (@.name == ""'::text || a.value) || '""))'::text))::jsonpath))"
           Rows Removed by Filter: 999714
 Planning Time: 0.164 ms
 Execution Time: 7244.288 ms

查询计划先运行了Function Scan on jsonb_array_elements_text，actual time=2454.358..7244.266 rows=3 loops=1表示实际运行时间是time=2454.358..7244.266中第一个时间2454.358是启动时间、第二个时间是7244.266是最终运行时间，rows=3返回了3行结果，loops=1表示循环一次

SubPlan 1表示运行子查询，Seq Scan on library表示在 library表上运行顺序查询，actual time=2414.746..2414.746 rows=1 loops=3表示实际运行时间是time=2414.746..2414.746中第一个时间2414.746是启动时间、第二个时间是2414.746是最终运行时间，rows=1返回了1行结果，loops=3表示循环三次

Rows Removed by Filter: 999714表示通过帅选排除了999714行

一看查询计划就会发现，啊为什么没有走索引，根据作者的上一篇文章，使用@?应该是是会走gin索引的。但是我们仔细看SQL中的比较是发生在JSON PATH语句中，并且其中的a.value需要在运行时才能确定。所以PostgreSQL的索引选择器不使用索引了？详细原因还没有查到，说得不对请指教。。。

下面使用其他的JSON操作符也不会走索引

explain analyze select exists(
    select 1 from library where delete_status=false and
                            library.params @> ('{"name":"' || a.value ||'"}')::jsonb
) from jsonb_array_elements_text('["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]'::jsonb) a;

解决也简单，我们强制使用索引就行了。

 set enable_seqscan= off;
 explain analyze select exists(
     select 1 from library where delete_status=false and
                             library.params @?? ('($.catalog[*] ? (@.name == "'|| a.value ||'"))') :: jsonpath
 ) from jsonb_array_elements_text('["PcYAxtOBpt", "jjYgoXiRcK", "aaa"]'::jsonb) a;

看返回果然时间缩短到了0.084ms，查询计划也显示走了索引查询

 Function Scan on jsonb_array_elements_text a  (cost=0.00..11579.12 rows=100 width=1) (actual time=0.044..1.455 rows=3 loops=1)
   SubPlan 1
     ->  Bitmap Heap Scan on library  (cost=113.44..22868.06 rows=9739 width=0) (actual time=0.480..0.480 rows=1 loops=3)
 "          Recheck Cond: (params @? ((('($.catalog[*] ? (@.name == ""'::text || a.value) || '""))'::text))::jsonpath)"
           Filter: (NOT delete_status)
           Heap Blocks: exact=2
           ->  Bitmap Index Scan on library_params_gin_params_catalog_jsonb_path_ops  (cost=0.00..111.01 rows=10000 width=0) (actual time=0.468..0.468 rows=1 loops=3)
 "                Index Cond: (params @? ((('($.catalog[*] ? (@.name == ""'::text || a.value) || '""))'::text))::jsonpath)"
 Planning Time: 0.084 ms

子查询

PostgreSQL的查询优化器还是很强大的，一般的子查询都会进行优化，采用子查询上拉等方式。但是在查看查询计划的时候，可以发现索引并没有得带优化。背后的原因作者还没有来得急分析，感觉有点复杂了。。。

当前只针对这个SQL来说优化方式有两种，在数据库会话级别开启强制索引set enable_seqscan= off;，想要数据库全局开启也可以，这个可以由项目自己把控。

还有另一种就是优化SQL语句，采用SQL拼接的方法：

 explain analyze select exists(select 1 from library where delete_status=false and
                             library.params @?? ('($.catalog[*] ? (@.name == "PcYAxtOBpt" || @.name == "jjYgoXiRcK" || @.name == "aaa"))'));

这个语句一定会走索引

 Result  (cost=68.75..68.76 rows=1 width=1) (actual time=0.026..0.027 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Bitmap Heap Scan on library  (cost=64.77..450.60 rows=97 width=0) (actual time=0.025..0.025 rows=1 loops=1)
 "          Recheck Cond: (params @? '$.""catalog""[*]?((@.""name"" == ""PcYAxtOBpt"" || @.""name"" == ""jjYgoXiRcK"") || @.""name"" == ""aaa"")'::jsonpath)"
           Filter: (NOT delete_status)
           Heap Blocks: exact=1
           ->  Bitmap Index Scan on library_params_gin_params_catalog_jsonb_path_ops  (cost=0.00..64.75 rows=100 width=0) (actual time=0.019..0.019 rows=2 loops=1)
 "                Index Cond: (params @? '$.""catalog""[*]?((@.""name"" == ""PcYAxtOBpt"" || @.""name"" == ""jjYgoXiRcK"") || @.""name"" == ""aaa"")'::jsonpath)"
 Planning Time: 0.485 ms
 Execution Time: 0.050 ms

总结

但是我们从中我们可以学到再写完SQL的时候还是要用explain analyze来看一下执行计划的，避免没有预想到的情况发生。并且对于PostgreSQL中的子查询的优化方式和JSON处理方式，需要有所了解才能再面对项目问题的有方法解决。

avatar
文章
阅读
粉丝