我得到了下表：

CREATE TYPE "weekday" AS ENUM ('mo', 'tu', 'we', 'th', 'fr', 'sa', 'su');

CREATE TABLE index_test
(
    school_id          uuid    NOT NULL,
    record_number      bigint  NOT NULL,
    weekday            weekday NOT NULL,
    room_record_number int     NULL,
    from_time          time    NOT NULL,
    to_time            time    NOT NULL,
    from_date          date    NOT NULL,
    to_date            date    NULL,
    CONSTRAINT "index_test_pkey" PRIMARY KEY (school_id, record_number)
);
CREATE INDEX "temp_multiple_idx" ON index_test (school_id, weekday, room_record_number, from_time, to_time);
ALTER TABLE index_test ENABLE ROW LEVEL SECURITY;

表的RLS策略如下：

CREATE POLICY "separate_schools" ON "index_test"
    USING (school_id = nullif(current_setting('mm_cloud.rls_restricted_by_school_id', true), '')::uuid);

还存在一个名为index_test的用户，RLS适用于该用户：

CREATE USER index_test ENCRYPTED PASSWORD 'index_test';
GRANT SELECT ON TABLE index_test to index_test;

我用吸尘器打扫table：

VACUUM (ANALYSE , VERBOSE) index_test;

然后我使用index_test用户运行以下查询：

SET mm_cloud.rls_restricted_by_school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193';
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM index_test
WHERE weekday = 'we'
  AND room_record_number = 3
  AND from_time = '15:15'
  AND to_time = '16:00';

这将生成以下查询计划：

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Scan using temp_multiple_idx on public.index_test  (cost=0.43..2020.22 rows=1 width=56) (actual time=0.785..0.786 rows=0 loops=1)                                                                                                                                                                     |
|  Output: school_id, record_number, weekday, room_record_number, from_time, to_time, from_date, to_date                                                                                                                                                                                                     |
|  Index Cond: ((index_test.school_id = (NULLIF(current_setting('mm_cloud.rls_restricted_by_school_id'::text, true), ''::text))::uuid) AND (index_test.room_record_number = 3) AND (index_test.from_time = '15:15:00'::time without time zone) AND (index_test.to_time = '16:00:00'::time without time zone))|
|  Filter: (index_test.weekday = 'we'::weekday)                                                                                                                                                                                                                                                              |
|  Buffers: shared hit=200                                                                                                                                                                                                                                                                                   |
|Planning Time: 0.144 ms                                                                                                                                                                                                                                                                                     |
|Execution Time: 0.806 ms                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

请注意，只使用了索引的一部分。
然后，我运行一个类似的查询，其中RLS规则内联，使用表所有者用户没有应用RLS：

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM index_test
WHERE school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193'
  AND weekday = 'we'
  AND room_record_number = 3
  AND from_time = '15:15'
  AND to_time = '16:00';

这会导致使用完整索引更好查询计划：

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Scan using temp_multiple_idx on public.index_test  (cost=0.42..8.45 rows=1 width=56) (actual time=0.073..0.074 rows=0 loops=1)                                                                                                                                                                |
|  Output: school_id, record_number, weekday, room_record_number, from_time, to_time, from_date, to_date                                                                                                                                                                                             |
|  Index Cond: ((index_test.school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193'::uuid) AND (index_test.weekday = 'we'::weekday) AND (index_test.room_record_number = 3) AND (index_test.from_time = '15:15:00'::time without time zone) AND (index_test.to_time = '16:00:00'::time without time zone))|
|  Buffers: shared hit=3                                                                                                                                                                                                                                                                             |
|Planning Time: 0.260 ms                                                                                                                                                                                                                                                                             |
|Execution Time: 0.128 ms                                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• 在Postgres 15.2和14.7上测试。
• 该表有119277行。