postgresql 索引状态_postgreSql索引没有被用到?-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_39620099/article/details/111482790

在PostgreSQL数据库中，对于一个包含100W行数据的notice表，使用索引area_type_time_desc在查询时能有效过滤数据，但当加入`order by public_time desc`进行排序时，查询效率降低，未利用索引进行排序，导致耗时增加。尝试单独为`public_time desc`创建索引也无法改善情况。寻求解决此类索引未被有效利用进行排序的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我现在有一个数据库, 表 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 ~

" 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 ~

"Planning Time: 0.636 ms"

"Execution Time: 214.591 ms"

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

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

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