PostgreSQL查询效率求助

hxz_rx

附件内容是游戏项目中用于生成物品数值的查询, 执行整个文件大约需要2小时, 但把查询一段一段复制到navicat上前后几分钟就执行完了.
查询主要卡连表update上, 表都建了索引, 因此执行单挑查询很快, 不解的是执行整个查询文件却很慢.
请各位大神不吝赐教, 谢谢.
查询很多是在excel中生成的, 比较恶心, 请见谅.

ps: 谢谢唐成老师在oc上的指教.

附件不能传sql文件, 压缩成rar了.
升星.rar (3.67 KB, 下载次数: 29)

2015-06-12 15:21 上传

点击文件名下载附件
查询文件

osdba

你是用psql -f sqlfile.sql这样执行的吗？

hxz_rx

回复 2# osdba

是的, 但发现psql -f xx.sql花费的时间与把xx.sql全部内容复制到navicat执行的时间差不多.

osdba

看了你的脚本也看不出什么问题。而你的这个脚本只能在你的环境中运行（因为一些其它的表不在这个脚本中），所以也无法帮助调试。你可以自行调试，如你可以设置参数log_min_duration_statement = 10000，然后再运行你的脚本，然后运行时间超过10000毫秒(10秒)的SQL都会打印到日志中，然后你再分析一下这些SQL为什么慢。

hxz_rx

回复 4# osdba

改log_min_duration_statement后把整个文件都打出来了, 感觉是pgsql把整个文件的东西当成一个超大事务来跑, 导致花费大量时间, 一行一行其实用不了多少时间的.
我把全部sql跟pgsql配置都打了个包, 按readme里面的步骤可以执行下去, 唐老师有空帮再看下, 不胜感激.
全部查询.rar (194.58 KB, 下载次数: 24)

2015-06-15 14:10 上传

点击文件名下载附件

osdba

我在我的服务器上试了一下，可以在5分16秒运行完：
$time psql -Uu01 -f 升星.sql > my.log
psql:升星.sql:5: NOTICE:  table "star_config_part_attrib_quota" does not exist, skipping
psql:升星.sql:39: NOTICE:  table "star_config_attack_increace_rate" does not exist, skipping
psql:升星.sql:85: NOTICE:  table "star_config_defence_increace_rate" does not exist, skipping
psql:升星.sql:131: NOTICE:  table "player_equip_star_value" does not exist, skipping
psql:升星.sql:328: NOTICE:  table "sum_up_player_lv_reincarnation_equip_star" does not exist, skipping
psql:升星.sql:376: NOTICE:  table "star_all_item_star_numeric" does not exist, skipping

real        5m16.772s
user        0m0.065s
sys        0m0.009s

osdba

在我的笔记本电脑上也只运行了16分钟：
osdba-mac:pgtest osdba$ time psql u01 -f 升星.sql > my2.log
psql:升星.sql:5: NOTICE:  table "star_config_part_attrib_quota" does not exist, skipping
psql:升星.sql:39: NOTICE:  table "star_config_attack_increace_rate" does not exist, skipping
psql:升星.sql:85: NOTICE:  table "star_config_defence_increace_rate" does not exist, skipping
psql:升星.sql:131: NOTICE:  table "player_equip_star_value" does not exist, skipping
psql:升星.sql:328: NOTICE:  table "sum_up_player_lv_reincarnation_equip_star" does not exist, skipping

psql:升星.sql:376: NOTICE:  table "star_all_item_star_numeric" does not exist, skipping

real        16m41.132s
user        0m0.207s
sys        0m0.012s

osdba

慢的SQL:
update player.sum_up_player_lv_reincarnation_equip_star x
set
health         = a.health         + b.health,
magic          = a.magic          + b.magic,
physic_attack  = a.physic_attack  + b.physic_attack,
magic_attack   = a.magic_attack   + b.magic_attack,
daoshu_attack  = a.daoshu_attack  + b.daoshu_attack,
physic_defence = a.physic_defence + b.physic_defence,
magic_defence  = a.magic_attack   + b.magic_defence
from
player.sum_up_player_basic a,
equipment.player_equip_star_value b
where x.career = a.career
and a.career = b.career
and x.lv = a.lv
and a.lv = b.lv
and x.star = b.star
and a.god_type = b.god_type
;
可以加两个索引，加快更新的速度：
create index on player.sum_up_player_lv_reincarnation_equip_star(career, lv, star);
create index on equipment.player_equip_star_value(career, lv, star);

osdba

在每个表的大规模更新后，加上analyze <tablename>;语句。因为每次更新后马上执行查询，统计信息还没有收集上来，会导致差的执行计划，所以要手工执行analyze <tablename>收集统计信息，保证后续的SQL的执行计划正确。而你分开执行快的原因是你等了一会儿，数据库自动帮你收集了统计信息的原因。我加上analyze <tablename>语句后，在我的笔记本上1分54秒就可以运行出来了：
osdba-mac:pgtest osdba$ time psql u01 -f 升星.sql > my2.log

real        1m54.936s
user        0m0.235s
sys        0m0.015s


加analyze的示例如下：
....
insert into equipment.player_equip_star_value(career, lv, god_type, star)
select a.career, a.lv, a.god_type, b.star
from
player.sum_up_player_basic a,
config.basic_star_open b
order by a.career, a.lv, a.god_type, b.star
;
create index on equipment.player_equip_star_value(career);
create index on equipment.player_equip_star_value(lv);
create index on equipment.player_equip_star_value(god_type);
create index on equipment.player_equip_star_value(star);
create index on equipment.player_equip_star_value(career, lv, star);
analyze equipment.player_equip_star_value;
....

hxz_rx

多谢唐老师, 问题解决了, 就是analyze的问题, 现在56秒跑完
谢谢谢谢...