postgresql 大数据量检索效率问题

Question

现在我有下面这段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遍遍历结果集处理我觉得也不会快啊望高人指教

Answer 1

你的索引情况，在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
这样试试

Answer 2

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

Answer 3

有人说先全件检索出来，只是order by一下，然后在后台java代码里去做to_char和group by 的工作，相当于把sql文的工作拿到后台去做---------------
这个是不可取的
因为那么多的数据传回客户端，这就是个大问题了

Answer 4

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

Answer 5

在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

Answer 6

CREATE INDEX createdate_cindex ON table1(createdate)就这样建立索引就可以了？好像检索起来还是很慢阿

Answer 7

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

Answer 8

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

Answer 9

估计是
“SELECT count(*) FROM table1 WHERE createdate > '某时间' ”
符合的记录数太多了，这样读取的IO量当然也大，自然就慢了不信你可以把'某时间'的范围指定为尽量的短测试下（如> '昨天')

Answer 10

同意楼上的.
用于网站的数据库设计，要多点沉余。另：postgresql可以建立表达式索引
to_char(timegenerated,'YYYY-MM-DD')

Answer 11

根据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);