0

Consider the following table

CREATE TABLE public.my_table
(
    my_id text COLLATE pg_catalog."default" NOT NULL,
    my_second_id text COLLATE pg_catalog."default" NOT NULL,

    CONSTRAINT my_table_pkey PRIMARY KEY (my_id, my_second_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

Querying the following

explain select * from my_table
where my_id = ''


explain select * from my_table
where my_second_id = ''

Have the following result

Index Scan using my_table_pkey on my_table  (cost=0.56..178699.70 rows=268 width=917)


Index Cond: (my_second_id = ''::text)


Index Scan using my_table_pkey on my_table  (cost=0.56..3544.14 rows=1142 width=917)


Index Cond: (my_id = ''::text)

I expect the second query that uses the right most index to do a full table scan, however I see index scan instead

Does postgres support sub index scan in composite index or am I missing something?

1 Answer 1

2

I can't replicate this without going to extreme contortions, like setting enable_seqscan, enable_bitmapscan, and enable_indexonlyscan all to off. What version are you using, what are you non-default settings, and how did you populate the data (as shown, you would get an empty table to query)?

But anyway, what it is doing here is a full index scan, using the index as a skinny table (although in your case, not much skinnier than the table itself), and applying the condition as an in-index filter. Unfortunately, the displayed plan does not distinguish between the "Index Cond:" is being used for jump-to selectivity, versus just as an in-index filter. But you can see the cost estimate for the query that uses it as an in-index filter is far higher (50 fold) than the one where it uses jump-to selectivity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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