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!

CodePudding user response：

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.