I have a very large table in postgreql 9.6 that has a composite primary key. Recently, I added an id column, back-filled the ids and added a unique index on it. But when I do basic lookups it does a sequential scan instead of using the index.

explain select * from table where id = 1000;

output:

Seq Scan on table  (cost=0.00..17897134.60 rows=1 width=1074)
  Filter: (id = 1000)

Here's the schema

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS table_id_seq;

-- Table Definition
CREATE TABLE "public"."table" (
    "co_no" int4 NOT NULL,
    "parcel_id" varchar(26) NOT NULL,
    "asmnt_yr" int4 NOT NULL,
    -- rest of columns are omitted
    "id" int4 NOT NULL DEFAULT nextval('table_id_seq'::regclass),
    PRIMARY KEY ("co_no","asmnt_yr","parcel_id")
);

CREATE UNIQUE INDEX "table_index_id" ON "public"."table" USING BTREE ("id");

CREATE UNIQUE INDEX "table_index_primary" ON "public"."table" USING BTREE 
("co_no","asmnt_yr","parcel_id");

Any idea as to why it won't use the existing index? Does it have to do with using such an old PG version?

EDIT Adding the constraint fixes this issue. Thanks /u/pehrs