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

Question

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)

I may be wrong, but AFAIK an index is implicit whenever a UNIQUE, PRIMARY KEY, or FOREIGN KEY is defined. In addition, in this case, the PRIMARY KEY is the target of a foreign key. That's why I'm confused whether an explicit index is necessary.

Answer 1

Postgres never creates an index for a foreign key column. See e.g. Automatic index creation for primary vs. foreign keys in Postgresql

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

If any of above is true for your system, it would be a good idea to add the 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 often 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).