Does it make sense to create an index on foreign key (uuid) column in Postgres?

Assuming 2 sample tables in Postgres 10.X:

CREATE TABLE public.order (
id       VARCHAR(36) NOT NULL,
...
)

CREATE TABLE public.suborder (
id       VARCHAR(36) NOT NULL,
order_id VARCHAR(36) NOT NULL,
...
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES public.order(id)
)

All IDs are simple UUIDs. suborder is being queried by order_id quite often. Does it make sense to create a separate index on order_id even though it references unique (UUID) value?

Something like:

CREATE INDEX suborder_order_idx ON public.suborder(order_id)

It's usually recommended to have an index on foreign key column. It helps when master and detail tables are frequently joined or when delete/update happens on master table. In this case absence of index causes full scan of detail table to enforce foreign key.

If any of above is true for your system , it would be a good idea to add index.

Side note. You mentioned ids store guid values, so probably you never search by range. Then hash index would be much better choice compared to "normal" b-tree index.

Indexing the foreign key column is also useful if the parent table receives deletes (or updates on the PK). For every row in the parent table that is deleted, the database has to check the referencing tables if they still have rows referencing the parent. That check is done by selecting from the child table with a where condition on the FK column. Obviously this is faster if the FK column is indexed (this is true for other DBMS like Firebird, Oracle, SQL Server or DB2 as well)

StackExchange Database Administrators Q#225271, answer score: 4