sqlpostgresqlindexingmany-to-manyjunction-table

Junction table indexing in many-to-many relationship


Let's say we have a PRODUCT table, ORDER table and a junction table PRODUCT_ORDER.

I create a junction table with the following script:

CREATE TABLE public."PRODUCT_ORDER" (

    product_id bigint NOT NULL,
    order_id bigint NOT NULL,

    CONSTRAINT "PRODUCT_ORDER_PK" PRIMARY KEY (product_id, order_id),

    CONSTRAINT "FK_TO_PRODUCT" FOREIGN KEY (product_id)
        REFERENCES public."PRODUCT" (id) ON DELETE CASCADE,

    CONSTRAINT "FK_TO_ORDER" FOREIGN KEY (order_id)
        REFERENCES public."ORDER" (id) ON DELETE CASCADE
);

Also an index for the PK was created automatically:

CREATE UNIQUE INDEX "PRODUCT_ORDER_PK" ON public."PRODUCT_ORDER" USING btree (product_id, order_id)

It is expected that there will be practically only read operations for these tables and I would like to index the junction table intelligently.

Usually, I additionally create an index for foreign keys manually, e.g. for one-to-many relationship, like this:

CREATE INDEX "index_name" ON schema_name."table_name" (fk_column_name);

My question is: Do I need to create two indexes for foreign keys in addition to PK-index like this:

 CREATE INDEX "FK_TO_PRODUCT" ON public."PRODUCT_ORDER" (product_id);
 CREATE INDEX "FK_TO_ORDER" ON public."PRODUCT_ORDER"(order_id);

Or is it unnecessary, meaningless and the index for the PK will be enough?

In general, I'm interested in how to intelligently/correctly index the junction tables for many-to-many relationship?

If you need any clarification, let me know and thanks in advance!


Solution

  • No, you do not need to create two additional keys. Your primary key index will work for the first key (product_id) as well as the pair.

    You need one additional index on order_id.