Does a postgres foreign key imply an index?

Question

I have a postgres table (lets call this table Events) with a composite foreign key to another table (lets call this table Logs). The Events table looks like this:

CREATE TABLE Events (
   ColPrimary UUID,
   ColA VARCHAR(50),
   ColB VARCHAR(50),
   ColC VARCHAR(50),
   PRIMARY KEY (ColPrimary),
   FOREIGN KEY (ColA, ColB, ColC) REFERENCES Logs(ColA, ColB, ColC)
);

In this case, I know that I can efficiently search for Events by the primary key, and join to Logs.

What I am interested in is if this foreign key creates an index on the Events table which can be useful even without joining. For example, would the following query benefit from the FK?

SELECT * FROM Events
WHERE ColA='foo' AND ColB='bar'

Note: I have run the POSTGRES EXPLAIN for a very similar case to this, and see that the query will result in a full table scan. I am not sure if this is because the FK is not helpful for this query, or if my data size is small and a scan is more efficient at my current scale.

Gothurd

Answer 1

PostgreSQL does not automatically create an index on the columns on which a foreign key is defined. If you need such an index, you will have to create it yourself.

It is usually a good idea to have such an index, so that modifications on the parent table that affect the referenced columns are efficient.

Connection

In many cases people do not modify or even delete PK entries, but even in these cases you may want to add indexes on FK columns if you find these columns are used in join scenarios/lookups. Since indexes aren't free, be sure to verify that with EXPLAIN. – Ataghan