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;


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;
  1. 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?

  • Yes, it will affect only index 2, (unless the update changes the state_id from 20 to 10). – ypercubeᵀᴹ Sep 17 '20 at 13:43
  • I will be really appreciated if you suggest me a link to some docs to prove it) I've read an official Postgres documentation and haven't found any mentions about insert or update performance for partial index, only about index size and select performance. – Markeli Sep 17 '20 at 14:21
  • 1
    From the official documentation: postgresql.org/docs/current/indexes-partial.html "This reduces the size of the index, which will speed up those queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases." – ypercubeᵀᴹ Sep 17 '20 at 14:51
  • Apparently I didn't read it carefully. Thanks! You can answer the question, and I will accept it. – Markeli Sep 17 '20 at 14:55
  • 1
    Please do not crosspost. Already asked and answered here: stackoverflow.com/questions/63939302/… – a_horse_with_no_name Sep 17 '20 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.