pg 的前辈们进来帮看看索引的问题

我现在有一个数据库, 表 notice 大概 100W row 左右的数据

表结构如下:

CREATE TABLE public.notice
(
    id integer NOT NULL DEFAULT nextval('notice_id_seq'::regclass),
    title character varying(200) COLLATE pg_catalog."default" NOT NULL,
    tags integer[] DEFAULT '{}'::integer[],
    public_time timestamp without time zone,
    content text COLLATE pg_catalog."default",
    type integer NOT NULL,
    site character(20) COLLATE pg_catalog."default",
    url character varying(1024) COLLATE pg_catalog."default",
    area character(10) COLLATE pg_catalog."default",
    CONSTRAINT notice_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

索引如下:

CREATE INDEX area_type_time_desc
    ON public.notice USING btree
    (area COLLATE pg_catalog."default" bpchar_pattern_ops, type, public_time DESC)
    TABLESPACE pg_default;

如下查询语句:

explain analyze select id, public_time, title, area from notice where area ~ '^110' and type=31  limit 10

输出:

"Limit  (cost=0.43..39.49 rows=10 width=122) (actual time=0.930..0.977 rows=10 loops=1)"
"  ->  Index Scan using area_type_time_desc on notice  (cost=0.43..67845.25 rows=17369 width=122) (actual time=0.928..0.971 rows=10 loops=1)"
"        Index Cond: ((area ~>=~ '110'::bpchar) AND (area ~<~ '111'::bpchar) AND (type = 31))"
"        Filter: (area ~ '^110'::text)"
"Planning Time: 0.532 ms"
"Execution Time: 1.020 ms"

可以看出索引是生效的!

当加上排序:

explain analyze select id, public_time, title, area from notice where area ~ '^110' and type=31 order by public_time desc limit 10

输出:

"Limit  (cost=51918.94..51918.97 rows=10 width=122) (actual time=214.522..214.530 rows=10 loops=1)"
"  ->  Sort  (cost=51918.94..51962.36 rows=17369 width=122) (actual time=214.520..214.522 rows=10 loops=1)"
"        Sort Key: public_time"
"        Sort Method: top-N heapsort  Memory: 29kB"
"        ->  Bitmap Heap Scan on notice  (cost=2012.57..51543.60 rows=17369 width=122) (actual time=30.013..205.214 rows=18635 loops=1)"
"              Recheck Cond: (type = 31)"
"              Filter: (area ~ '^110'::text)"
"              Heap Blocks: exact=15625"
"              ->  Bitmap Index Scan on area_type_time_desc  (cost=0.00..2008.23 rows=17362 width=0) (actual time=23.107..23.107 rows=18635 loops=1)"
"                    Index Cond: ((area ~>=~ '110'::bpchar) AND (area ~<~ '111'::bpchar) AND (type = 31))"
"Planning Time: 0.636 ms"
"Execution Time: 214.591 ms"

排序耗时非常长, 没有用到索引排序

我尝试只建立一个 public_time desc 的索引, 查询不加任何条件只有一个 order by 同样排序要很耗时

请前辈们指正一下我哪里用的用问题, 我查了好多文档, 没有找到解决方案, 特来求助