问当启用行级安全性而不使用防泄漏操作符时，PostgreSQL会忽略pg_trgm GIN索引

EN

首先，这个这样的问题描述了一个类似的问题：PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled，但是我未能成功地利用它的建议，我还想看看是否有一种方法可以在Postgraphile的上下文中进行改进。

复制步骤：

作为超级用户，创建一个简单的表并使用一些随机数据填充它：

CREATE TABLE public.videos AS SELECT id, md5(random()::text) AS title from generate_Series(1,1000000) id;

执行ILIKE查询(在本文中，它称为"ILIKE查询“，用于多次测试性能)：

EXPLAIN ANALYSE SELECT COUNT(*) FROM public.videos WHERE title ILIKE '%test%';

如预期的那样，它执行Seq扫描，执行时间约为194.823 ms。

安装gp_trgm扩展并添加一个gin索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE INDEX trgm_idx_videos_title ON public.videos USING gin (title gin_trgm_ops);

由于表中已经填充了数据，创建索引需要一些时间(~10秒)。现在，运行相同的ILIKE查询将使用“trgm_idx_videos_title上的位图索引扫描”，执行时间为0.036 ms。

到目前为止，一切看起来都很好，但前提是您总是可以使用超级用户来获取数据，而不是为所述表实现额外的安全性。

让我们设置一个额外的用户，并授予它访问连接到我们的数据库(名为gin_rls_test)的权限

CREATE ROLE db_login WITH LOGIN PASSWORD 'db_login_pwd' NOINHERIT;
GRANT CONNECT ON DATABASE gin_rls_test TO db_login;

我们还需要为先前为上述用户创建的表授予选择权限。

GRANT SELECT ON public.videos TO db_login;

为了确保我们的用户能够像以前一样查询数据，可以使用db_login连接到数据库服务器。(在pgAdmin中，您只需创建一个新服务器，指定一些不同的名称，相同的主机名，但使用db_login/db_login_pwd作为用户名和密码)

如果您使用新添加的db_login连接导航到我们的表，打开一个查询工具并执行相同的ILIKE查询-结果应该是相同的，索引将被应用。

破坏这一点的是RLS (行级安全)。让我们切换回超级用户的查询编辑器，并为我们的表设置它：

ALTER TABLE public.videos ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.user_has_permission() returns boolean LANGUAGE plpgsql as $$
BEGIN
   return true;
END;
$$;

CREATE POLICY videos_authorization ON public.videos FOR SELECT USING (public.user_has_permission());

为了简单起见，public.user_has_permission()函数只返回true。(在我的示例中，它是一个plpgsql函数，它根据存储在pg_catalog.current_setting中的设置检查权限，不执行任何额外的显式请求。)

现在，如果您从超级用户查询编辑器运行ILIKE查询，它仍将像以前一样快，因为superuser避免了rls。如果您从db_login查询编辑器运行它，索引将不再被击中，Seq扫描将被使用，执行时间将在1013.485 ms左右。

在阅读了这个线程(https://www.postgresql.org/message-id/CAGrP7a3PwDYJhPe53yE6pBPPNxk2Ve4n%2BdPQMS1HcBU6swXYfA%40mail.gmail.com)之后，似乎出现了这个问题，因为ILIKE的底层函数并不是防泄漏的。“解释分析”告诉我们，我们使用运算符~*进行文本比较，运行此查询将使您知道该运算符的底层函数名称：

SELECT * FROM pg_operator WHERE oprname = '~~*';

实际上有3种结果，在我们的例子中，底层函数名是“类似文本的”。您可以做的是切换到超级用户查询编辑器，并使该函数防泄漏：

ALTER FUNCTION texticlike LEAKPROOF;

现在，如果您再次从db_login查询编辑器运行ILIKE查询，则索引将被击中，执行时间将返回到0.040 ms。

问题：

也许使某些操作符显式防泄漏是可以接受的，但真正的问题(除了意外泄漏某些东西，例如，如果抛出异常)是只有超级用户才能使函数防泄漏。如果您拥有Azure或AWE托管数据库，您将没有超级用户访问权，并将得到以下错误，试图使函数防泄漏：

ERROR:  only superuser can define a leakproof function
SQL state: 42501

因此，对于我来说，如何使GIN索引在启用RLS的表上为ILIKE查询工作，仍然是一个未回答的问题。或者，在仍然将title属性作为文本类型时，实现相同性能结果的替代方案是什么？

我正在使用Postgraphile，我对改进"includesInsensitive“和"startsWithInsensitive”过滤器的性能很感兴趣，ILIKE操作符防泄漏也会影响这些过滤器。

附加信息：

如果您想要将功能切换回“不防泄漏”：

ALTER FUNCTION texticlike NOT LEAKPROOF;

我试图使用此查询查找所有可用的防泄漏操作符，但没有在其中找到任何可行的替代方法(最接近的是运算符^@的"starts_with“函数，它区分大小写)：

select pg_proc.proname, pg_operator.oprname, pg_operator.oprcode, pg_proc.proleakproof from pg_proc
join pg_operator ON pg_proc.proname::text = pg_operator.oprcode::text
where pg_proc.proleakproof;

使用gp_trgm gin索引的最初想法来自于本文：https://niallburkley.com/blog/index-columns-for-like-in-postgres/

PostgreSQL版本(使用SELECT version();) - "PostgreSQL 12.2 on x86_64-pc-linux-musl，由gcc (高山9.2.0) 9.2.0，64位编译“