5

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)
Improve this question
7
  • After we remove all irrelevant information, your question is this: "Table A is queried frequently by the value of column X. Does it make sense to create an index on column X?" -- so, what's the alternative, in your opinion?
    – mustaccio
    Dec 18, 2018 at 15:40
  • 3
    Unrelated, but: do not store UUIDs as varchar(36) use a uuid column.
    – a_horse_with_no_name
    Dec 18, 2018 at 15:42
  • @mustaccio sort of. But the main point is that the column's content is a foreign key on UUID of another table, and UUID is always unique by design. Question is still the same. Sorry if it's too verbose.
    – Mikhail Kholodkov
    Dec 18, 2018 at 16:34
  • What does it have to do with the decision to have an index or not? And how does Postgres know that your VARCHAR(36) column contains something that is "unique by design"?
    – mustaccio
    Dec 18, 2018 at 16:49
  • 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 being the target of a foreign key. That's why I'm confused whether an explicit index is necessary.
    – Mikhail Kholodkov
    Dec 18, 2018 at 17:11

1 Answer 1

Reset to default
4

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)

Improve this answer
3
  • Thanks for the answer! So PostgreSQL doesn't implicitly creates an index on FOREIGN KEY? Even though if column's type is uuid? And even though FOREIGN KEY references PRIMARY KEY?
    – Mikhail Kholodkov
    Dec 18, 2018 at 17:13
  • 1
    @MikhailKholodkov: first: your columns are varchar not uuid. Second: Postgres (or actually any DBMS I know) never creates an index for a foreign key column. See e.g. here or here
    – a_horse_with_no_name
    Dec 18, 2018 at 17:17
  • 3
    @MikhailKholodkov : as far as I remember, only Mysql does (or at least did) that. There are valid cases when index on FK will not give any benefits, only extra maintenance cost. Say, you have small lookup table for currencies. Account table will very likely have FK to that Currency table, but due to low selectivity and absence of DML against Currency , there is no benefits on indexing this FK column
    – a1ex07
    Dec 18, 2018 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.