postgresql 大数据量检索效率问题

2009-10-30 09:52:37 1.7k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Postgresql杂谈 23——Postgresql中的全文检索今天我们来聊一下全文检索，想必做搜索相关业务朋友对这个概念不会陌生，尤其是做搜索引擎，或者类似CSDN、知乎类的社区网站，全文检索是逃不开的业务。文，即文章、文档。全文搜索就是给定关键词，在所有的文档数据中找到符合关键词的文档。不管是哪种业务模式下的全文检索功能，其实大体的实现思路类似，如下所示：使用文字进行描述，就是：（1）获取原始文档数据。（2）对文档进行分析，分词（所为分词，就是按照分词符，如空格，将一句话分隔成若干的单词）（3）存档存入数据库，并通... 阅读详情

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

...全文

PostgreSQL 高级特性实战：分区表、并行查询与插件生态深度应用摘要：PostgreSQL高级特性（分区表、并行查询、插件生态）是应对千万级/亿级数据量、复杂查询场景的核心解决方案。分区表通过数据分片存储可将大表查询速度提升10倍；并行查询利用多核CPU使复杂统计查询性能提升3-5倍；丰富的插件生态（如pg_partman、timescaledb）无需二次开发即可扩展时序处理、全文检索等高级功能。本文结合1亿级数据场景，详细解析三大特性的实现原理、企业级配置方案和性能优化技巧，提供可直接复用的技术方案，帮助开发者突破传统数据库性能瓶颈。通过合理组合这些特性，可显著提升大阅读详情

kbryant

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

kangde 2009-11-01

打赏
举报

学习了

ACMAIN_CHM 2009-10-30

打赏
举报

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

WWWWA 2009-10-30

打赏
举报

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

kbryant 2009-10-30

打赏
举报

CREATE INDEX createdate_cindex ON table1(createdate)

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

ACMAIN_CHM 2009-10-30

打赏
举报

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

ACMAIN_CHM 2009-10-30

打赏
举报

同意楼上，及楼上的楼上

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

WWWWA 2009-10-30

打赏
举报

在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

trainee 2009-10-30

打赏
举报

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

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

vinsonshen 2009-10-30

打赏
举报

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

vinsonshen 2009-10-30

打赏
举报

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

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

wwwwb 2009-10-30

打赏
举报

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

WWWWA 2009-10-30

打赏
举报

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

vinsonshen 2009-10-30

打赏
举报

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

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

[/Quote]

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

vinsonshen 2009-10-30

打赏
举报

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

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

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

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

postgresql 大数据量检索效率问题

相关推荐