主页 / user-317381

snorris's questions

Martin Hope
snorris
Asked: 2024-11-22 02:33:45 +0800 CST
  • 5

PG17 注释的第一个不兼容说明：

需要引用非默认模式的表达式索引和物化视图所使用的函数必须在函数创建期间指定搜索路径。

ltree使用依赖于所安装扩展的函数重新创建物化视图（在 PG 14-16 中运行良好）public在 PG17 上失败，原因如下：

ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

如果我set search_path = public按照不兼容性说明的建议添加函数定义，则可以创建物化视图。

但是一旦set search_path添加，该功能就不再可用 - 规划器不会获取ltree类型的索引。

有没有一种解决方法可以让物化视图中使用的函数来选择ltree运算符public？或者促使规划器使用索引？还是我做错了什么？

在全新的 PG 17.0 数据库上进行重现（在 dockerized 上也基本相同PostgreSQL 17.1 (Debian 17.1-1.pgdg110+1)）：

select version();
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 (Homebrew) on aarch64-apple-darwin24.1.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit

-- check the default search path
SELECT setting FROM pg_settings WHERE name = 'search_path';
     setting
-----------------
 "$user", public
(1 row)


-- setup test data
create extension if not exists ltree;

create schema test;

create table test.table
(id serial primary key,
 code ltree);

insert into test.table (code)
select
  concat_ws('.',
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0')
)::ltree as code
from generate_series(1, 1000000) s(i);

create index on test.table using gist (code);
create index on test.table using btree (code);

-- create test function as per pg16 (no search path)
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the function - it picks up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.41..15541.90 rows=100000 width=4) (actual time=0.105..1.078 rows=11 loops=1)
   ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.015..0.019 rows=10 loops=1)
         ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.014..0.016 rows=10 loops=1)
   ->  Index Scan using table_code_idx on "table" a  (cost=0.41..1454.16 rows=10000 width=36) (actual time=0.086..0.104 rows=1 loops=10)
         Index Cond: (code @> "table".code)
 Planning Time: 1.475 ms
 Execution Time: 1.175 ms
(7 rows)

-- try and use the function in a materialized view,
-- (works fine in PG14,PG16, fails on PG17)
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a
                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT
  code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

-- try specifying search_path as recommended in
-- PG17 release notes
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean set search_path = public language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the new function - it works, but does not pick up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2645310.33 rows=3333333 width=4) (actual time=0.335..6668.876 rows=11 loops=1)
   Join Filter: test.myfunc(a.code, b.code)
   Rows Removed by Join Filter: 9999989
   ->  Seq Scan on "table" a  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.034..46.823 rows=1000000 loops=1)
   ->  Materialize  (cost=0.00..0.35 rows=10 width=32) (actual time=0.000..0.000 rows=10 loops=1000000)
         ->  Subquery Scan on b  (cost=0.00..0.30 rows=10 width=32) (actual time=0.008..0.012 rows=10 loops=1)
               ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.008..0.011 rows=10 loops=1)
                     ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.006..0.007 rows=10 loops=1)
 Planning Time: 0.313 ms
 Execution Time: 6668.912 ms
(10 rows)

-- Try using the updated func in materialized view
-- The view is successfully created (on this small sample), 
-- but is impractical on larger data because it doesn't use the index
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
SELECT 11
postgresql