关于postgreSQl索引问题

[saul_yuan]

期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：


设在postgreSQl数据库中有一张表Student_Assessment(Student_id, assessment_id, score)
通过查询pg_class系统表，得到的物理存储如下：
relname | reltuples | relpages
student_assessment | 249999 | 2942
在未建立表索引的情况下，对该表的一个查询进行explain和analyze工具分析
explain analyze select student_id, assessment_id
from studengt_assessment
where score between 50 and 80;
结果为： ...(cost=0.00..6691.98 rows=78990 width=8) (actual time=114.35..790.73 rows=77833 loops=1) Total runtime:847.33 msec
然后建了个btree索引
create index student_assessment_index on student_assessment using btree(score);
然后重新分析查询
explain analyze select student_id, assessment_id
frome stuent_assessment
where score between 50 and 80;
结果为: ...(cost=0.00..6692.02 rows=78991 width=8) (actual time=233.38..907.74 rows=77835 loops=1) Total runtime:963.98 msec
从以上分析可看出建索引后擦汗讯性能下降。
问（1）下降的可能原因；（2）提出能真正提高查询性能的索引方案


分虽不多，但是一片诚心，希望有大神能够详解，尽量别3言2语带过，如果有空可否能说说你们平时优化索引的经验么！？不甚感谢！

[trainee]

这个题目还有点偏
还是你描述的不完全？

有以下很重要的事项你描述不完全：所以不知道出题者的意图
1、score的数据类型
2、“结果为： ...”这省略号写的是什么？

[saul_yuan]

[Quote=引用 1 楼 trainee 的回复:]
这个题目还有点偏
还是你描述的不完全？

有以下很重要的事项你描述不完全：所以不知道出题者的意图
1、score的数据类型
2、“结果为： ...”这省略号写的是什么？
[/Quote]

score数据类型的话一般分数应该是浮点吧！这个问题很重要么！？题既然没给你类型，那说明没类型也可以做出来。
至于你说的省略号这也是小问题，难道你在建索引的时候没有出现Seq scan on student_assessment，省略号就这这语句，我懒得打就用...代替了

[saul_yuan]

没人回么！？明天就考试了诶

[ACMAIN_CHM]

为什么不愿意问你一下你的老师？

你的老师可以收了你的钱的，结果竟然不教你，反而需要你自己到论坛来试图找个人免费的回答你。

[iihero]

[Quote=引用楼主 saul_yuan 的回复:]
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：


设在postgreSQl数据库中有一张表Student_Assessment(Student_id, assessment_id, score)
通过查询pg_class系统表，得到的物理存储如下：
relname | reltuples | relpages
……
[/Quote]
score between 50 and 80;
要看看这个区间的记录占总记录的百分比了。并不是建了索引就一定快。
如果这之间的记录占相当大的部分，那么，索引起的作用就不明显了，甚至更慢。

[saul_yuan]

[Quote=引用 4 楼 acmain_chm 的回复:]
为什么不愿意问你一下你的老师？

你的老师可以收了你的钱的，结果竟然不教你，反而需要你自己到论坛来试图找个人免费的回答你。
[/Quote]
去年的题...丫的老师出差了，我倒是想问啊！

[saul_yuan]

[Quote=引用 5 楼 iihero 的回复:]
引用楼主 saul_yuan 的回复:
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：


设在postgreSQl数据库中有一张表Student_Assessment(Student_id, assessment_id, score)
通过查询pg_class系统表，得到的物理存储如下：
relname | reltuples | relpages……
[/Quote]
这个说法还可以接受，78990/249999=31.6%，这个比例不知算不算大。不知你对第二题有没有什么思路

[iihero]

[Quote=引用 7 楼 saul_yuan 的回复:]

引用 5 楼 iihero 的回复:
引用楼主 saul_yuan 的回复:
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：
这个说法还可以接受，78990/249999=31.6%，这个比例不知算不算大。不知你对第二题有没有什么思路
relname |……
[/Quote]
2）提出能真正提高查询性能的索引方案?
索引方案，我已经说过，如果你要取的结果占整个表数据的比例比较大(意味着物理IO很多)，建了索引，用处也不太大。
不过，你不妨对score按成绩分段进行分区。

[saul_yuan]

[Quote=引用 8 楼 iihero 的回复:]
引用 7 楼 saul_yuan 的回复:

引用 5 楼 iihero 的回复:
引用楼主 saul_yuan 的回复:
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：
这个说法还可以接受，78990/249999=31.6%，这个比例不知算不算大。不知你对第二题有没有什么思路
relname |……

2）提出能真正提高查询性能的索引方案?
……
[/Quote]
你的意思是指分成若干个小区间然后再并到一块！？

[iihero]

[Quote=引用 9 楼 saul_yuan 的回复:]

引用 8 楼 iihero 的回复:
引用 7 楼 saul_yuan 的回复:

引用 5 楼 iihero 的回复:
引用楼主 saul_yuan 的回复:
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：
这个说法还可以接受，78990/249999=31.6%，这个比例不知算不算大。不知你对第二题有没有什么思路
relname |……

……
[/Quote]
实际上，已经没有必要创建索引了。

[saul_yuan]

[Quote=引用 10 楼 iihero 的回复:]
引用 9 楼 saul_yuan 的回复:

引用 8 楼 iihero 的回复:
引用 7 楼 saul_yuan 的回复:

引用 5 楼 iihero 的回复:
引用楼主 saul_yuan 的回复:
期末数据库考试最后有道索引题，看不太明白，求大神指导。是这样的，我简单说一下题目：
这个说法还可以接受，78990/249999=31.6%，这个比例不知算不算大。不知你对第二……
[/Quote]
望着这份卷子，我D真是生T生T的。算了，也就4分，到时候蒙不上也没关系，老师大概提了一下索引，但没说索引怎么优化，结果TN的还考。gan!

[iihero]

第2题，可以这样回答，如果结果集占总的数据集的比例很小，比如，5%以内，索引还可起到减少IO读取的作用，从而提高效率，但是如果实际的结果集占的数据集的比例太高，超过15%或者更多，索引起到的作用将大大减弱，甚至比不用索引所执行的IO读取的次数还要多，这时候，就没有优化的必要了。