Why Postgres uses a custom defined index instead of the default one on the primary key

Question

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 alpha_id and 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

pk_alpha

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.

---

Additional info

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.

Answer 1

Due to padding for memory alignment, adding a small column to an index can often take up no extra room. The index might even be smaller, if it is fresher and so more densely packed. And even if not, the cost estimate for looking up one row in an index is very weakly dependent on index size, so the cost is likely to be a tie between the two indexes. When there is a tie between indexes they are broken arbitrarily, and it seems like it is the one created most recently that is usually chosen.