in postgres, can one optimize a table across partitions?

in a database with many companies, segregating them using schema and search_path

almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting functions need to scan all schema.

for this problem, per @horse_with_no_name's most excellent suggestion, using the schema themselves as partitions. for example

create table global.usr(
    usr_id int primary key,  // these will be unique across all partitions
    name varchar );

create table cmp1.usr(
    usr_id int primary key,
) inherits( global.usr );

create table cmp2.usr( 
    ....

if we populate

insert into cmp1 usr values( 11, 'eleven' );
insert into cmp1 usr values( 12, 'twelve' );

insert into cmp2 usr values( 21, 'twenty-one' );
insert into cmp2 usr values( 22, 'twenty-two' );

insert into cmp3 usr values( 31, 'thirty-one' );
insert into cmp3 usr values( 32, 'thirty-two' );

this works beautifully at the schema level:

set search_path = 'global';
select count(1) from t;   => 6

set search_path = 'cmp1, global';
select count(1) from t;   => 2

set search_path = 'cmp1, cmp2, global';
select count(1) from t;   => 4

set search_path = 'global, cmp1';
select count(1) from t;   => 6

the question is whether there is any way to optimize at the global level. for example, would it be possible to create an index on global.usr_id that would span all partitions?

the question is whether there is any way to optimize at the global level. for example, would it be possible to create an index on global.usr_id that would span all partitions?

Sadly the answer is "No" - global indexes are not currently supported

StackExchange Database Administrators Q#10373, answer score: 4