PostgreSQL查询效率求助
附件内容是游戏项目中用于生成物品数值的查询, 执行整个文件大约需要2小时, 但把查询一段一段复制到navicat上前后几分钟就执行完了.查询主要卡连表update上, 表都建了索引, 因此执行单挑查询很快, 不解的是执行整个查询文件却很慢.
请各位大神不吝赐教, 谢谢.
查询很多是在excel中生成的, 比较恶心, 请见谅.
ps: 谢谢唐成老师在oc上的指教.
附件不能传sql文件, 压缩成rar了.
你是用psql -f sqlfile.sql这样执行的吗？ 回复 2# osdba
是的, 但发现psql -f xx.sql花费的时间与把xx.sql全部内容复制到navicat执行的时间差不多. 看了你的脚本也看不出什么问题。而你的这个脚本只能在你的环境中运行（因为一些其它的表不在这个脚本中），所以也无法帮助调试。你可以自行调试，如你可以设置参数log_min_duration_statement = 10000，然后再运行你的脚本，然后运行时间超过10000毫秒(10秒)的SQL都会打印到日志中，然后你再分析一下这些SQL为什么慢。 回复 4# osdba
改log_min_duration_statement后把整个文件都打出来了, 感觉是pgsql把整个文件的东西当成一个超大事务来跑, 导致花费大量时间, 一行一行其实用不了多少时间的.
我把全部sql跟pgsql配置都打了个包, 按readme里面的步骤可以执行下去, 唐老师有空帮再看下, 不胜感激.
我在我的服务器上试了一下，可以在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 在我的笔记本电脑上也只运行了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 慢的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); 在每个表的大规模更新后，加上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;
.... 多谢唐老师, 问题解决了, 就是analyze的问题, 现在56秒跑完 :)
谢谢谢谢...
页:
[1]