Update and insert performance with partial indexes

Question

I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query. For example, the table similar to this:

CREATE TABLE public.contacts (
    id int8 NOT NULL DEFAULT ssng_generate_id(8::bigint),
    created timestamp NOT NULL DEFAULT timezone('UTC'::text, now()),
    contact_pool_id int8 NOT NULL,
    project_id int8 NOT NULL,
    state_id int4 NOT NULL DEFAULT 10,
    order_x int4 NOT NULL,
    next_attempt_date timestamp NULL,
    CONSTRAINT contacts_pkey PRIMARY KEY (id)
);

And there are two types of query:

SELECT * FROM contacts WHERE contact_pool_id = X AND state_id = 10 ORDER BY order_x LIMIT 1;

and

SELECT * FROM contacts WHERE contact_pool_id = X AND state_id = 20 AND next_attemp_date <= NOW ORDER BY next_attemp_date LIMIT 1;

For those queries I've created partial indexes:

1. For state_id = 10 (new contacts)

CREATE INDEX ix_contacts_cpid_orderx_id_for_new ON contacts USING btree (contact_pool_id, order_x, id) WHERE state_id = 10;

2. For state_id = 20 (available contacts)

CREATE INDEX ix_contacts_cpid_nextattepmdate_id_for_available ON contacts USING btree (contact_pool_id, next_attempt_date, id) WHERE state_id = 20;

For me, those partial indexes are faster than a single index.

And what about an update and insert performance? If I change a row with state_id = 20, will it affect only index 2 (for available contacts) or both of them will be affected?

Answer 1

Yes, with a partial index you only pay the overhead of modifying the index for rows that meet the WHERE condition, so you will always only need to modify at most one of the indexes at the same time (unless you change state_id from 10 to 20 or vice versa).

Answer 2

Partial indexes which are not relevant to the tuple will not get updated.

If PostgreSQL can do a HOT update (if the column being changed is not part of an index, and there is room on the same page for the new tuple), then even the relevant index doesn't need to get updated.