sql - 发布 || 聚合耗时

Question

下面的查询需要 5-6 秒才能执行。有什么办法可以改进吗？表中有大约 190 万条记录，符合条件的记录为 90 万条

询问：

Select startdate,  perosntypecode,  verificationstatuscode, activitynumber, enddate, activityname,  COUNT(*)
from personmoderation
where  startdatetimegmt >= '2018-06-12T10:00:56'
and embarkdate between (SELECT p.startdate FROM personmoderation AS p 
                     WHERE p.startdatetimegmt >= '2018-06-12T10:00:56' 
                     ORDER BY p.startdatetimegmt ASC LIMIT 1
                   ) 
                and (SELECT p.startdate FROM personmoderation AS p
                     WHERE p.startdatetimegmt >= '2018-06-12T10:00:56' 
                     ORDER BY p.startdatetimegmt ASC LIMIT 1
                   ) + interval '100 days' 
group by startdate,  perosntypecode,  verificationstatuscode, activitynumber, enddate, activityname

指数：

create
    index ix1 on
    personmoderation
        using btree(
        startdatetimegmt,
        startdate,
        persontypecode,
        verificationstatuscode,
        activitynumber,
        enddate,
        activityname
);

注意：where 子句中使用的子查询需要 3-4 毫秒。

标签： sqlpostgresqlperformanceoptimizationindexing