我在 High Sierra 上使用 PostgreSQL 9.5。
在两个表上:
request_logs - ~ 26K 行 response_logs - ~ 9K 行
我有以下查询(使用 JOIN):
选择 req.uuid, 资源状态, 请求方法, req.requesturi, 请求访问, req.payload reqpayload, res.payload 重载, COUNT(*) OVER() AS total_rows 从 request_logs 请求 左外连接 response_logs res ON req.uuid = res.uuid 在哪里 req.accountid = 2 和 req.requesturi 不喜欢 '/v1/sessions%' AND req.accessed BETWEEN “2018-01-01 15:04:05 +0000” 和 “2019-01-02 15:04:05+0000” AND res.status 不为空并且 req.applicationid = 1 订购方式 访问 DESC LIMIT 1000
当我尝试优化查询时,我尝试了不同的索引:这是我尝试过的列表:
配置一: 1. request_log.uuid(pkey,唯一) 2. response_log.uuid(pkey,唯一,外键) 平均响应时间 : 260 毫秒
配置 2: 1. request_log.uuid(pkey,唯一) 2.request_log.applicationid 3. response_log.uuid(pkey,唯一,外键) 平均响应时间 : 230 毫秒
配置三: 1. request_log.uuid(pkey,唯一) 2.request_log.applicationid 3. request_log.accessed(时间戳) 4. response_log.uuid(pkey,唯一,外键) 平均响应时间 : 230 毫秒
配置 4: 1. request_log.uuid(pkey,唯一) 2.request_log.applicationid 3. request_log.accessed(时间戳) 4.request_log.accountid 5. response_log.uuid(pkey,唯一,外键) 平均响应时间 : 230 毫秒
配置 5: 1. request_log.uuid(pkey,唯一) 2. request_log.applicationid, request_log.accessed (合并) 3. response_log.uuid(pkey,唯一,外键) 平均响应时间 : 240 毫秒
从结果中可以看出,通过applicationid
(an int8
) 进行索引确实有一点帮助,而通过 the 进行索引timestampz
accessed
根本没有帮助。也许糟糕的表现是由于加入?总之,它看起来很慢,我尽量不去想当这些表包含数百万条记录(10M+)时会发生什么。
为这些表编制索引以使该查询运行得更快的更好方法是什么?
编辑:
这是EXPLAIN ANALYZE
最后的配置:
Limit (cost=3489.80..3490.69 rows=356 width=823) (实际时间=241.152..241.345 rows=1000 loops=1) -> 排序(成本=3489.80..3490.69 行=356 宽度=823)(实际时间=241.150..241.288 行=1000 循环=1) 排序键:req.accessed DESC 排序方式:top-N堆排序内存:2064kB -> WindowAgg(成本=1829.41..3474.71 行=356 宽度=823)(实际时间=230.040..237.993 行=3951 循环=1) -> Hash Join (cost=1829.41..3470.26 rows=356 width=823) (actual time=8.622..17.974 rows=3951 loops=1) 哈希条件:(res.uuid = req.uuid) -> response_logs res 上的序列扫描(成本=0.00..1604.21 行=8821 宽度=758)(实际时间=0.006..4.527 行=9124 循环=1) 过滤器:(状态不为空) -> Hash (cost=1816.39..1816.39 rows=1042 width=102) (actual time=8.243..8.243 rows=4046 loops=1) 桶:4096(原为 2048)批次:1(原为 1)内存使用:1122kB -> 位图堆扫描 request_logs req (cost=105.85..1816.39 rows=1042 width=102) (actual time=0.581..6.449 rows=4046 loops=1) 执行时间:242.154 毫秒
对于初学者,获得
LEFT JOIN
权利并尝试摆脱总行数(如评论中所述):询问
更新到您的评论:
你
LEFT [OUTER] JOIN
被烧成了一片平原[INNER] JOIN
。有关的:COUNT(*) OVER() AS total_rows
LIMIT
对于比(并且您期望“10m+ 行”)大得多的计数来说是昂贵的。也许它足够好使用LIMIT 1001
,只使用前 1000 行,检查行数,如果它是 1001,那么你知道有“超过 1000 个匹配行”。有关的:索引
如果(就像你评论的那样)
... 和 上的其他谓词排除了多行
applicationid
,requesturi
然后部分索引应该有助于读取性能(很多):accountid
如果通过 消除了相当大比例的行
status IS NOT NULL
,则还有:如果您可以从中获得仅索引扫描,则可能需要附加列
status
并payload
作为索引列。一些先决条件适用。