On a PostgreSQL 14.5 database, there is the following table:
CREATE TABLE IF NOT EXISTS public."Points"
(
"Key" character varying(50) COLLATE pg_catalog."default" NOT NULL,
"ReferencedKeys" text[] COLLATE pg_catalog."default" NOT NULL,
"State" integer NOT NULL,
"OtherKey" text COLLATE pg_catalog."default",
CONSTRAINT "PK_Points" PRIMARY KEY ("Key")
)
CREATE INDEX IF NOT EXISTS "IX_Points_OtherKey"
ON public."Points" USING btree
("OtherKey" COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "IX_Points_ReferencedKeys"
ON public."Points" USING gin
("ReferencedKeys" COLLATE pg_catalog."default")
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "IX_Points_State"
ON public."Points" USING btree
("State" ASC NULLS LAST)
TABLESPACE pg_default;
The table contains roughly 2e7 records with the following distribution of State:
| State | Count |
|---|---|
| 2 | 1900000 |
| 4 | 200 |
There are 4100 different OtherKeys.
Now the relevant statement which takes about 10 s:
UPDATE "Points" AS i
SET "State" = 0
WHERE i."OtherKey" = 'MyKeyId' AND i."State" NOT IN (0, 4, 3);
EXPLAIN in pgAdmin shows this:
The blurred Key conditions refer to OtherKey.
From my gut feeling, I'd have thought that PostgreSQL would choose the index IX_Points_OtherKey as it's more selective, which doesn't seem to be the case.
So I'd really appreciate it if someone could explain why PostgreSQL prefers the index IX_Points_State and how I could improve this query.
Thank you!
