I have a table called
alpha with many columns, the primary key is just
alpha_id. I have also an index (
super_index) on that table that uses
status, which is just another column.
I run a query against it,
EXPLAIN SELECT alpha_id, name, descr, data, to_char(past_date, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'), status, failure, FROM job WHERE alpha_id = 'example_value';
and despite the fact that Postgres has created automatically an index for the primary key
and that my only condition is using it, this one is never used, and
super_index is always visited. Here is also the result of the explain command,
Index Scan using super_index on alpha (cost=0.14..8.16 rows=1 width=798) Index Cond: ((alpha_id)::text = 'example_value'::text)
I have read that all the decisions are based on statistics, but I do not understand the logic behind this one.
The table and
super_index are actually created using Liquibase, but this is what I get using pgAdmin,
CREATE TABLE public.alpha ( alpha_id character varying(48) COLLATE pg_catalog."default" NOT NULL, name character varying(255) COLLATE pg_catalog."default", descr text COLLATE pg_catalog."default", data text COLLATE pg_catalog."default", past_date timestamp without time zone NOT NULL, status alpha_status NOT NULL DEFAULT 'Suspended'::alpha_status, percentage_complete double precision NOT NULL DEFAULT 0.00, failure text COLLATE pg_catalog."default", CONSTRAINT pk_alpha PRIMARY KEY (alpha_id) ) CREATE INDEX super_index ON public.alpha USING btree (alpha_id COLLATE pg_catalog."default", alpha_status) TABLESPACE pg_default;
pg_alpha is the index created by Postgres by default on the primary key, and I do not have it listed in the Indexes list on pgAdmin, so I do not know how to retrieve its definition.