0

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.

  • Can you please show the actual table definition (as a CREATE TABLE statement) with both indexes? – dezso Jan 28 at 14:47
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.

  • "When there is a tie between indexes they are broken arbitrarily, and is seems like it is the one created most recently that is usually chosen." I have tried to delete the index, and Postgres started to use the default one. EXPLAIN ANALYZE gave basically the same cost for both indexes. So, I think that you may be right. I will wait for one day to see if any other reply arrives, and then I will mark this as accepted otherwise. – Pampa Nello Jan 28 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.