我看到过针对各种personid和不同类型的查询需要不到10毫秒的时间,也看到它需要16分钟的时间。发生了什么事?
Column | Type | Modifiers
--------------+------------------+--------------------------------------------------------
id | bigint | not null default nextval('record_seq'::regclass)
type | integer | not null
personid | integer |
reporttime | bigint | not null
totalreading | double precision | not null
delta | double precision | not null
Indexes:
"record_pkey" PRIMARY KEY, btree (id)
"record_personid_idx" btree (personid)
"record_type_idx" btree (type)
"record_reporttime_idx" btree (reporttime) CLUSTER
这是对查询的解释性分析,有时非常缓慢。
explain analyze SELECT ID, TYPE, REPORTTIME, TOTALREADING, DELTA, PERSONID FROM RECORD WHERE PERSONID=1103 AND TYPE=405 AND REPORTTIME <= 1332447354000 ORDER BY REPORTTIME DESC LIMIT 1;
Limit (cost=0.00..327.93 rows=1 width=52) (actual time=239749.274..239749.274 rows=0 loops=1)
-> Index Scan Backward using record_reporttime_idx on record (cost=0.00..1196290.82 rows=3648 width=52) (actual time=239749.251..239749.251 rows=0 loops=1)
Index Cond: (reporttime <= 1332447354000::bigint)
Filter: ((personid = 1103) AND (type = 405))
Total runtime: 239749.409 ms
大约有10-20种类型,其中2种使用最频繁,405种使用频率几乎没有那么高。
select count(*) from record;
count
----------
30420232
SELECT COUNT(*) FROM record WHERE PERSONID=1103 AND TYPE=405;
count
-------
58
因为您正在搜索某个目标之前的最后一个报告时间值,所以规划者认为向后搜索是有意义的。
它可能在某些/大多数时候都会这样做,但偶尔你不得不走很长的路才能找到(personid,type)的正确组合。
如果您通常在(personid,type)上进行搜索,请尝试对这两列,甚至可能对所有三列进行组合索引。三列的顺序以及是否需要保留其他索引将取决于查询的总组合。