postgresql 大数据量检索效率问题

[kbryant]

现在我有下面这段sql文

SELECT to_char(timegenerated,'YYYY-MM-DD') as timeStr，type as type,count(*) AS num
FROM table1
WHERE
createdate > '某时间'
GROUP BY timeStr,type

现在我table1表里有1000w条数据，执行这段sql文时速度比较慢，怎么优化下，主要是sql文里用到了函数to_char，还有GROUP BY 什么的，影响了速度，有人说先全件检索出来，只是order by一下，然后在后台java代码里去做to_char和group by 的工作，相当于把sql文的工作拿到后台去做，这样效率会比现在的高吗？全部拿出来1000w条哦，后台要循环1000w遍遍历结果集处理我觉得也不会快啊。。。望高人指教

[vinsonshen]

[Quote=引用 6 楼 kbryant 的回复:]
CREATE INDEX createdate_cindex ON table1(createdate)

就这样建立索引就可以了？好像检索起来还是很慢阿

[/Quote]
估计是
“SELECT count(*) FROM table1 WHERE createdate > '某时间' ”
符合的记录数太多了，这样读取的IO量当然也大，自然就慢了

不信你可以把'某时间'的范围指定为尽量的短测试下（如> '昨天')

[vinsonshen]

[Quote=引用 6 楼 kbryant 的回复:]
CREATE INDEX createdate_cindex ON table1(createdate)

就这样建立索引就可以了？好像检索起来还是很慢阿

[/Quote]

像这样的统计需求，表数据有点多，从你语句上来说，索引上优化也只能这个程度了
其实，我觉得应该在表设计上进行优化，根据数据量设计一个“最近明细数据表”，然后每天将前一天的数据进行汇总统计，然后将统计结果放进一个“汇总数据表”，统计完后，马上将前一天的明细数据备份进“历史明细表”，然后“最近明细数据表”对应天的数据清理掉。
这样，像你上面的统计需求，只需要对“汇总数据表”进行统计就可以了(速度会快很多)。

[WWWWA]

你的索引情况，在createdate上建立索引试试
SELECT to_char(timegenerated,'YYYY-MM-DD') as timeStr，type as type AS num into newtt
FROM table1 WHERE createdate > '某时间'

select timeStr，type as type,count(*) AS num from newtt GROUP BY timeStr,type
这样试试

[wwwwb]

OR
在生成NEWTT后，在timeStr,type上建立索引再分组

[vinsonshen]

有人说先全件检索出来，只是order by一下，然后在后台java代码里去做to_char和group by 的工作，相当于把sql文的工作拿到后台去做

---------------
这个是不可取的
因为那么多的数据传回客户端，这就是个大问题了

[vinsonshen]

列createdate要建立索引，从你上面的处理需求来看，这个是不可避免的了

[trainee]

同意楼上的.
用于网站的数据库设计，要多点沉余。

另：postgresql可以建立表达式索引
to_char(timegenerated,'YYYY-MM-DD')

[WWWWA]

在createdate上建立索引，这样试试
SELECT timeStr，type as type,count(*) AS num from (
SELECT *,to_char(timegenerated,'YYYY-MM-DD') as timeStr FROM table1 WHERE
createdate > '某时间' ) a
GROUP BY timeStr,type

[ACMAIN_CHM]

同意楼上，及楼上的楼上

其实有些数据仓库的概念进来了。

[ACMAIN_CHM]

根据11 楼的提示，创建基于表达式的索引！


CREATE INDEX xxxxx ON table1 (createdate, to_char(timegenerated,'YYYY-MM-DD'));
或者
CREATE INDEX yyyyy ON table1 (to_char(timegenerated,'YYYY-MM-DD'),createdate);

[kbryant]

CREATE INDEX createdate_cindex ON table1(createdate)

就这样建立索引就可以了？好像检索起来还是很慢阿

[WWWWA]

在postgresql中有无类似MYSQL　EXPLAIN的命令，检查在地方执行速度慢

[ACMAIN_CHM]

建立基于 (createdate,timegenerated) 的复合索引。但效果不会太大。因为你用到了函数 to_char

[kangde]

学习了