sql - 在 postgres 中，是否可以优化 UNION 的 VIEW

Question

数据库中有很多相同的模式，cmp01..cmpa0

每个模式都有一个users表

每个模式的 users 表的主键都有自己唯一的范围

例如，在 cmp01.users 中，usr_id 介于 0x01000000 和 0x01ffffffff 之间。

有什么方法可以定义一个 VIEW global.users，它是每个 cmp*.union 表的联合，如果查询通过 usr_id，优化器将转向正确的模式？

我的想法是这样的:

create view global.users as
select * from cmp01.users where usr_id between 0x01000000 and 0x01ffffffff
union all
select * from cmp02.users where usr_id between 0x02000000 and 0x02ffffffff
....

这行得通吗？不。 EXPLAIN ANALYZE 显示所有使用的模式。

是否有一种方法可以为优化器提供良好的提示？

Answer 1

为什么不在包含所有用户的公共(public)模式中创建一个表，可能还有一个额外的列来存储源模式。由于 id 是全局唯一的，您可以使 id 列保持唯一:

create table all_users (
source_schema varchar(32),
usr_id int primary key,
-- other columns as per existing table(s)
);

通过插入所有行来填充表格:

insert into all_users
select 'cmp01', * from cmp01.users union
select 'cmp02', * from cmp02.users union ...; -- etc

使用触发器使表保持最新。

设置起来并不难，而且性能会很好