in postgres, is it possible to optimize a VIEW of UNIONs

Question

in the database there are many identical schemas, cmp01..cmpa0

each schema has a users table

each schema's users table's primary key has its own unique range

for example, in cmp01.users the usr_id is between 0x01000000 and 0x01ffffffff.

is there any way I could define a VIEW global.users that is a union of each of the cmp*.union tables in such a way that, if querying by usr_id, the optimizer would head for the correct schema?

was thinking something like:

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
....

would this work? NO. EXPLAIN ANALYZE shows all schema used.

Is there an approach that might give good hints to the optimizer?

Answer 1

Why not create a table in a public schema that has all users in it, possibly with an extra column to store the source schema. Since the ids are globally unique, you could keep the id column unique:

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

Poluate the table by inserting all rows:

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

Use triggers to keep the table up to date.

It's not that hard to set up, and it will perform every well