9
回答
postgresql性能优化问题
【腾讯云】学生服务器套餐10元/月 >>>   

数据库服务器是centos6.7,8G,4核,postgresql版本8.4,postgresql.conf 文件已修改,配置如下:

fsync:off

shared_buffers:2G

work_mem:10MB

effective_cache_size:6G

maintenance_work_mem:512MB

checkpoint_segments:32

checkpoint_completion_target:0.9

wal_buffer:8MB

commit_delay:10

commit_siblings:4

现在数据库中有一个表table1,需要从其中查询数据,表字段有id,field1,field2,field3等。id类型为 character varing(50),已建索引。这3个字段( id,field1,field2)的索引也已创建。

语句如下:

select id,field1,field2,count(1) as cnt from table1 where id='id_num' group by id,field1,field2

表中数据175W。按照不同的id,数据量少的大概几十,多的70W,当查询只有几十的ID时,查询是通过id索引,速度很快,当数据量多时,走了全表扫描,需要30S左右。这个应该怎么优化?

举报
共有9个答案 最后回答: 1年前

引用来自“bogang2012”的评论

建包含这3个字段( id,field1,field2)的索引试试

这个索引是有的,我忘记写出来了。

引用来自“bogang2012”的评论

用explain去看看在哪花的时间多

"HashAggregate  (cost=89759.79..89804.11 rows=3545 width=20)"
"  ->  Seq Scan on table1  (cost=0.00..83348.77 rows=641102 width=20)"
"        Filter: ((id)::text = 'test143'::text)"
 

来的正是时候啊,看你Seq Scan那块,预计0.00实际83348,这块有毛病啊。

你索引列的数据范围大不大?

如果占比不是很大,可以试试下面这个设置,使用位图索引,这样能快很多。

set enable_bitmapscan = on;
set enable_indexscan = off;

 

--- 共有 3 条评论 ---
李察德-泰森 回复 @HeartArea : 加limit后,"Limit (cost=92872.95..92873.07 rows=10 width=20)" " -> HashAggregate (cost=92872.95..92917.26 rows=3545 width=20)" " -> Seq Scan on table1 (cost=0.00..86461.9 1年前 回复
HeartArea 回复 @李察德-泰森 : 有可能是数据量大,io时间长,你家上limit看速度咋样。 1年前 回复
李察德-泰森试过了,不管用的。索引列数据会非常多,多的时候预计有几百万。 1年前 回复

引用来自“HeartArea”的评论

来的正是时候啊,看你Seq Scan那块,预计0.00实际83348,这块有毛病啊。

你索引列的数据范围大不大?

如果占比不是很大,可以试试下面这个设置,使用位图索引,这样能快很多。

set enable_bitmapscan = on;
set enable_indexscan = off;

 

设置后,出来的是这样

"HashAggregate  (cost=92872.95..92917.26 rows=3545 width=20)"
"  ->  Seq Scan on tasks_clientquest  (cost=0.00..86461.93 rows=641102 width=20)"
"        Filter: (((taskid)::text = 'test143'::text) AND (interrupted = 0))"
 

引用来自“HeartArea”的评论

来的正是时候啊,看你Seq Scan那块,预计0.00实际83348,这块有毛病啊。

你索引列的数据范围大不大?

如果占比不是很大,可以试试下面这个设置,使用位图索引,这样能快很多。

set enable_bitmapscan = on;
set enable_indexscan = off;

 

引用来自“李察德-泰森”的评论

设置后,出来的是这样

"HashAggregate  (cost=92872.95..92917.26 rows=3545 width=20)"
"  ->  Seq Scan on tasks_clientquest  (cost=0.00..86461.93 rows=641102 width=20)"
"        Filter: (((taskid)::text = 'test143'::text) AND (interrupted = 0))"
 

不对啊,怎么还是seq_scan啊,你是怎么设置的?

引用来自“HeartArea”的评论

来的正是时候啊,看你Seq Scan那块,预计0.00实际83348,这块有毛病啊。

你索引列的数据范围大不大?

如果占比不是很大,可以试试下面这个设置,使用位图索引,这样能快很多。

set enable_bitmapscan = on;
set enable_indexscan = off;

 

引用来自“李察德-泰森”的评论

设置后,出来的是这样

"HashAggregate  (cost=92872.95..92917.26 rows=3545 width=20)"
"  ->  Seq Scan on tasks_clientquest  (cost=0.00..86461.93 rows=641102 width=20)"
"        Filter: (((taskid)::text = 'test143'::text) AND (interrupted = 0))"
 

引用来自“HeartArea”的评论

不对啊,怎么还是seq_scan啊,你是怎么设置的?

数据量少的走的是索引

"HashAggregate  (cost=141.62..141.64 rows=1 width=20)"
"  ->  Index Scan using table1_id on table1 (cost=0.00..140.79 rows=83 width=20)"
"        Index Cond: ((id)::text = 'test142'::text)"
 

--- 共有 1 条评论 ---
HeartArea正常来讲,用index还是seq scan,pg有个默认的阈值做分界线,这种情况只能说明那个设置根本没生效。 1年前 回复
顶部