why the postgresql did not using the multi-column index

Question

When I want to using this sql to check using index or not:

explain SELECT id AS id, sub_source_id AS sub_source_id 
FROM article 
WHERE sub_source_id IN (
select sub_source_id 
    from sub_relation sr 
    where user_id =14
    and sub_status = 1

) ORDER BY created_time DESC LIMIT 50

show info like this:

    Workers Planned: 2
    ->  Sort  (cost=74344.09..74697.64 rows=141422 width=24)
          Sort Key: article.created_time DESC
          ->  Hash Join  (cost=77.11..69646.15 rows=141422 width=24)
                Hash Cond: (article.sub_source_id = sr.sub_source_id)
                ->  Parallel Seq Scan on article  (cost=0.00..67792.05 rows=675805 width=24)
                ->  Hash  (cost=70.56..70.56 rows=524 width=8)
                      ->  Seq Scan on sub_relation sr  (cost=0.00..70.56 rows=524 width=8)
                            Filter: ((user_id = 14) AND (sub_status = 1))

I have already create multi-column index with column sub_source_id and created_time , why the sql did not using this article_sub_source_id_create_time_idx index? This is the table structure:

-- public.article definition

-- Drop table

-- DROP TABLE public.article;

CREATE TABLE public.article (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    user_id int8 NOT NULL,
    title varchar(512) NOT NULL,
    author varchar(256) NOT NULL,
    guid varchar(512) NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    link varchar(512) NULL,
    pub_time timestamptz NULL,
    sub_source_id int8 NOT NULL,
    cover_image varchar(1024) NULL,
    channel_reputation int4 NOT NULL DEFAULT 0,
    editor_pick int4 NULL DEFAULT 0,
    permanent_store int2 NOT NULL DEFAULT 0,
    CONSTRAINT article_id_seq_pk PRIMARY KEY (id),
    CONSTRAINT article_title_sub_source_id_key UNIQUE (title, sub_source_id)
);
CREATE INDEX article_sub_source_id_create_time_idx ON public.article USING btree (sub_source_id, created_time);
CREATE INDEX article_title_gin ON public.article USING gin (to_tsvector('english'::regconfig, (title)::text));
CREATE INDEX article_title_zh_gin ON public.article USING gin (to_tsvector('dolphinzhcfg'::regconfig, (title)::text));

when I remove the sub query and replace with static sub source id, the outer query could using the article_sub_source_id_create_time_idx index.

Answer 1

The planner thinks you will be fetching 20% of the table, before the sort and LIMIT are applied. (Is it correct?). Using an index to do that is probably a bad idea, unless it can use an index-only scan, or can use an ordering index scan and then stop early.

To get an index only scan, you would need the index to have all columns used for that table, (sub_source_id, created_time, id).

To get it to use the index for ordering, created_time would need to be the first column in the index (or at least it would need to follow only columns tested for simple equality, of which this query has none)