PostgreSQLSRF(set-returningfunctions)函式where,index實現

標籤

PostgreSQL , set-returning functions , SRF , 索引 , 轉換

---

背景

PostgreSQL 函式支援返回多行，定義returns setof type即可。PG內部也內建了蠻多這樣的函式，例如

select proname from pg_proc where proretset;  
  
generate_series  
unnest  
 json_array_elements  
 json_array_elements_text  
 json_object_keys  
 json_each  
 json_each_text  
 json_to_recordset  
 unnest  
 ts_stat  
 ts_stat  
 ts_token_type  
 ts_token_type  
 ts_parse  
 ts_parse  
 jsonb_array_elements  
 jsonb_array_elements_text  
 jsonb_object_keys  
 jsonb_each  
 jsonb_each_text  
 jsonb_populate_recordset  
 jsonb_to_recordset  
............  

自定義SRF方法如下：

https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html

對於返回多行的函式，如果在where條件中執行過濾，會返回如下錯誤。

ERROR:  0A000: set-returning functions are not allowed in WHERE  

如果要建立基於SRF函式的表示式索引，會報如下錯誤：

ERROR:  0A000: set-returning functions are not allowed in index expressions  

而實際上，可能在場景中有查詢srf = ?這樣的需求。

如何達到這樣的需求呢？

1、 可以將SRF再轉換為ARRAY，然後構建ARRAY的GIN索引。

例如這是個例子  
  
postgres=# create or replace function gen_rand(int,int) returns int[] as $$  
select array(select (random()*$1)::int from generate_series(1,$2));  
$$ language sql strict;  
CREATE FUNCTION  
  
postgres=# select gen_rand(100,10);  
           gen_rand              
-------------------------------  
 {17,5,33,70,54,10,89,96,5,94}  
(1 row)  

例子

1、建表

create table a (id int, js jsonb);  

2、寫入測試資料

postgres=# insert into a select id, jsonb_build_array((random()*1000)::int, (random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int,(random()*1000)::int) from generate_series(1,10000) t(id);  
INSERT 0 10000  

3、資料樣本

postgres=# select * from  a limit 2;  
 id |                        js                           
----+---------------------------------------------------  
  1 | [774, 509, 740, 813, 925, 67, 539, 730, 598, 465]  
  2 | [232, 153, 119, 895, 962, 501, 998, 609, 10, 410]  
(2 rows)  

4、某個JSON型別的SRF函式呼叫如下

postgres=# select jsonb_array_elements(js) from  a where id=1;  
 jsonb_array_elements   
----------------------  
 774  
 509  
 740  
 813  
 925  
 67  
 539  
 730  
 598  
 465  
(10 rows)  

在WHERE中過濾，建立表示式索引，均報錯

postgres=# set VERBOSITY verbose  
postgres=# select * from a where jsonb_array_elements(js) =1;  
ERROR:  0A000: set-returning functions are not allowed in WHERE  
LINE 1: select * from a where jsonb_array_elements(js) =1;  
                              ^  
LOCATION:  check_srf_call_placement, parse_func.c:2258  
  
  
  
postgres=# create index idx_a_1 on a (jsonb_array_elements(js));  
ERROR:  0A000: set-returning functions are not allowed in index expressions  
LINE 1: create index idx_a_1 on a (jsonb_array_elements(js));  
                                   ^  
LOCATION:  check_srf_call_placement, parse_func.c:2251  

5、建立一個UDF，將SRF結果轉換為ARRAY

postgres=# create or replace function srf_to_arr(jsonb) returns text[] as $$  
  select array(select jsonb_array_elements($1)::text);  
$$ language sql strict immutable;  
CREATE FUNCTION  

postgres=# select id,js,srf_to_arr(js) from a limit 1;  
 id |                        js                         |                srf_to_arr                  
----+---------------------------------------------------+------------------------------------------  
  1 | [774, 509, 740, 813, 925, 67, 539, 730, 598, 465] | {774,509,740,813,925,67,539,730,598,465}  
(1 row)  

6、WHERE srf = ? 條件變成如下

postgres=# select id,js,srf_to_arr(js) from a where srf_to_arr(js) @> array[`1`];  
  id  |                        js                        |               srf_to_arr                  
------+--------------------------------------------------+-----------------------------------------  
   18 | [96, 4, 447, 177, 53, 550, 768, 27, 1, 280]      | {96,4,447,177,53,550,768,27,1,280}  
   67 | [402, 1, 841, 834, 462, 769, 247, 568, 114, 690] | {402,1,841,834,462,769,247,568,114,690}  
  102 | [555, 599, 389, 719, 1, 916, 910, 637, 566, 36]  | {555,599,389,719,1,916,910,637,566,36}  
  162 | [687, 1, 628, 851, 20, 522, 883, 814, 874, 938]  | {687,1,628,851,20,522,883,814,874,938}  
.....  

7、建立表示式索引

postgres=# create index idx_a_1 on a using gin(srf_to_arr(js));  
CREATE INDEX  

用上了這個GIN倒排索引

postgres=# explain select id,js,srf_to_arr(js) from a where srf_to_arr(js) @> array[`1`];  
                              QUERY PLAN                                 
-----------------------------------------------------------------------  
 Bitmap Heap Scan on a  (cost=3.69..76.70 rows=50 width=160)  
   Recheck Cond: (srf_to_arr(js) @> `{1}`::text[])  
   ->  Bitmap Index Scan on idx_a_1  (cost=0.00..3.67 rows=50 width=0)  
         Index Cond: (srf_to_arr(js) @> `{1}`::text[])  
(4 rows)  

參考

https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html