PostgreSQL 14.5: Should I create 2 indexes what overlap?-CodePudding

I am using PostgreSQL 14.5 , SQL script

DROP TABLE IF EXISTS contract_detail_revenue;
CREATE TABLE contract_detail_revenue
(
    id                    smallint,
    contract_id           smallint       not null,
    cancel_revenue_date   date,
    revenue_type          smallint       not null,
    employee_id           smallint,
    organization_unit_id  smallint,
    inventory_item_id     smallint,
    rate                  numeric(16, 4) not null,
    revenue_amount        numeric(16, 4) not null,
    cancel_revenue_amount numeric(16, 4) not null,
    description           character varying(512),
    sort_order            smallint       not null,
    tenant_id             smallint,
    PRIMARY KEY (id, tenant_id)
);


CREATE INDEX contract_detail_revenue_idx ON contract_detail_revenue (id, tenant_id);
COMMIT;

I am sure that I will query with where clause use tenant_id very much. Should I create another index

CREATE INDEX contract_detail_revenue_idx2 ON contract_detail_revenue (tenant_id);

Sample query:

SELECT * FROM contract_detail_revenue WHERE tenant_id = 42;

I have many queries likes this (multi-tenant system).

CodePudding user response：

Your index contract_detail_revenue_idx is redundant, since the primary key will already create an index on these columns. Instead, create the primary key on (tenant_id, id) rather than (id, tenant_id). The order is irrelevant for the uniqueness guarantee, but the resulting index will have tenant_id first, so that it can be used by queries that have only tenant_id in their WHERE condition.

My advice is to come up with the actual queries you need before you define indexes.

CodePudding user response：

In fact, it don't has the limit like this(using the sample column to create index).

If you like or you need,you can do it.

But it better to if your query need, and you have to know that the size of two column's index(like contract_detail_revenue_idx) will lager then the one column(like contract_detail_revenue_idx2).

If where always like where id = $ and tenant_id = $, mayber contract_detail_revenue_idx will helper.If you never use it this way, I recommend creating separate indexes for the two fields.