I've been searching for this for a week now, and I'm afraid this may not exist [yet]. I wanted to use an index that spans multiple tables in PostgreSQL. Oracle and SQL server seem to implement them (with more or less options).
It could be quite useful for some searches I need to implement.
For reference, here are the multi-table index examples for Oracle and SQL Server:
Oracle Example
Oracle can create bitmap join indexes, as shown below:
create table dealer (
id int primary key not null,
city varchar2(20) not null
);
create table car (
id int primary key not null,
brand varchar2(20),
price int,
dealer_id int references dealer (id)
);
create bitmap index bix1 on car (d.city, c.brand)
from car c, dealer d
where d.id = c.dealer_id;
select avg(c.price)
from dealer d
join car c on c.dealer_id = d.id
where d.city = 'Chicago' and c.brand = 'Buick';
SQL Server Example
SQL Server can create indexed views:
create table dealer (
id int primary key not null,
city varchar(20) not null
);
create table car (
id int primary key not null,
brand varchar(20),
price int,
dealer_id int references dealer (id)
);
create view v with schemabinding as
select d.city, c.brand, c.price, c.dealer_id
from dbo.dealer d
join dbo.car c on c.dealer_id = d.id;
create unique clustered index uix1 on v (city, brand, price);
select avg(c.price)
from dealer d
join car c on c.dealer_id = d.id
where d.city = 'Chicago' and c.brand = 'Buick';