Search code examples
sqlpostgresqlunique

PostgreSQL Btree index on top of Unique index

I have a table with a unique index.

CREATE UNIQUE INDEX task_index ON public.task USING btree (id, user);

Most of my selects are using where id=?

So do I still need an index on ID column? Or the unique index itself indexed the column?

Solution

  • The unique index you already created uses a B tree with id as the first field to split with. This index should be usable for a where clause containing either only id or id and user. For the former case, consider a query like:

    SELECT *
FROM task
WHERE id = 3;

    Postgres can use your current index to find the subtree which corresponds to id = 3. For all leaf nodes beneath this subtree, it can simply scan to find all matching records.