Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does PostgreSQL implement multi-table indexes?

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';
like image 690
The Impaler Avatar asked Oct 15 '22 01:10

The Impaler


1 Answers

As of the current version of the PostgreSQL (v 12), an index can be based on a table or materialized view only.

https://www.postgresql.org/docs/current/sql-createindex.html

CREATE INDEX constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view.

The CREATE INDEX syntax requires a table and there can only 1 table specificed

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]

table_name:
The name (possibly schema-qualified) of the table to be indexed.

The materialized view is an option but, the data in materialized view is stale until you refresh the data.

https://www.postgresql.org/docs/12/sql-creatematerializedview.html

CREATE MATERIALIZED VIEW defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and may be refreshed later using REFRESH MATERIALIZED VIEW.

You maybe able to balance it out by automating a process to run REFRESH MATERIALIZED VIEW command in a way to reduce the likelihood of stale data. For example, after large data imports and at regular intervals at other times. But, if your data is large enough to require indexing, the refresh & re-indexing process will not be fast enough and thus you won't be able to execute it after every CRUD statement in an OLTP scenario.

In conclusion, what you are looking for does not exist in PostgreSQL as of v 12.

like image 61
K4M Avatar answered Oct 19 '22 22:10

K4M