我现在有一个数据库, 表 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 同样排序要很耗时
请前辈们指正一下我哪里用的用问题, 我查了好多文档, 没有找到解决方案, 特来求助
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.