postgresql如何提高这个SQL的查询效率？

Question

表结构：

CREATE TABLE log_temp
(
    logtime text,
    loginfo text
)

表中数据是一些日志类似：

logtime                loginfo
2016-10-11 16:54:00    ---Thread start---
2016-10-11 16:54:00    id:3 value:010001
...                    ...

查询SQL：

SELECT DISTINCT a.rownum
FROM (SELECT ROW_NUMBER() OVER() AS rownum, logtime, loginfo FROM log_temp) a
INNER JOIN log_temp b
ON a.logtime = b.logtime AND a.loginfo = b.loginfo AND b.loginfo like '%'||'Thread end'||'%'
ORDER BY a.rownum ASC

这个语句是查询所有包含Thread end字符串的行的行号，其中Thread end部分会根据程序运行中的不同替换成不同的字符串，所以两端用||做连接。

另外，这个表的数据每个小时都会被truncate然后再重新insert。

目前10万数据查询结果2000行+，用时15s，请问有没有什么办法可以提高这个SQL的查询效率？优化SQL也好，从postgresql本身的特性入手也好。谢谢。

Answer 1

为什么要用连接呢？

select rownum from
(select row_number() over() as rownum, * from log_temp) as log_rownum
where loginfo like '%'||'Thread end'||'%'

这样把连接去掉不知会不会快一些。

Answer 2

如果将 loginfo 写入时以字符串变化部分开头，再对这一列生成索引，这样速度会更快