CODEWITHSUNDEEP
sqlpostgresqlviewquery-optimization
cc young
cc young

Reputation: 20213

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

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?

Upvotes: 5

Views: 4980

Answers (3)

user330315
user330315

Reputation:

What about creating a partitioned table? The master table would be created as global.users and it would be partitioned by the schema name.

That way you'd get the small user tables in each schema (including fast retrievals) provided you can create queries that PostgreSQL can optimize i.e. including the schema name in the where condition. You could also create a view in each schema that would hide the needed schema name to query the partitioned tables. I don't think it would work by specifying only the user_id. I fear that PostgreSQL's partitioning features are not smart enough for that.

Or use just one single table, and create views in each schema with an instead of trigger and limiting the result to that schema's users.

Upvotes: 1

user359040
user359040

Reputation:

Try something like:

create view global.users as
select *
from (select 'cmp01' sel_schema, 0x01000000 usr_id_start, 0x01ffffffff usr_id_end
      union all
      select 'cmp02' sel_schema, 0x02000000 usr_id_start, 0x02ffffffff usr_id_end) s
join (select u1.*, 'cmp01' schema from cmp01.users u1
      union all
      select u2.*, 'cmp02' schema from cmp02.users u2) u
on s.sel_schema = u.schema

and include a condition like specified_usr_id between usr_id_start and usr_id_end when querying the view by a specified user ID.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425073

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

Upvotes: 3

Related Questions