PostgreSQL查詢效率求助

hxz_rx

附件內(nèi)容是游戲項(xiàng)目中用于生成物品數(shù)值的查詢, 執(zhí)行整個文件大約需要2小時(shí), 但把查詢一段一段復(fù)制到navicat上前后幾分鐘就執(zhí)行完了.
查詢主要卡連表update上, 表都建了索引, 因此執(zhí)行單挑查詢很快, 不解的是執(zhí)行整個查詢文件卻很慢.
請各位大神不吝賜教, 謝謝.
查詢很多是在excel中生成的, 比較惡心, 請見諒.

ps: 謝謝唐成老師在oc上的指教.

附件不能傳sql文件, 壓縮成rar了.
升星.rar (3.67 KB, 下載次數(shù): 29)

2015-06-12 15:21 上傳

點(diǎn)擊文件名下載附件
查詢文件

osdba

你是用psql -f sqlfile.sql這樣執(zhí)行的嗎？

hxz_rx

回復(fù) 2# osdba

是的, 但發(fā)現(xiàn)psql -f xx.sql花費(fèi)的時(shí)間與把xx.sql全部內(nèi)容復(fù)制到navicat執(zhí)行的時(shí)間差不多.

osdba

看了你的腳本也看不出什么問題。而你的這個腳本只能在你的環(huán)境中運(yùn)行（因?yàn)橐恍┢渌�的表不在這個腳本中），所以也無法幫助調(diào)試。你可以自行調(diào)試，如你可以設(shè)置參數(shù)log_min_duration_statement = 10000，然后再運(yùn)行你的腳本，然后運(yùn)行時(shí)間超過10000毫秒(10秒)的SQL都會打印到日志中，然后你再分析一下這些SQL為什么慢。

hxz_rx

回復(fù) 4# osdba

改log_min_duration_statement后把整個文件都打出來了, 感覺是pgsql把整個文件的東西當(dāng)成一個超大事務(wù)來跑, 導(dǎo)致花費(fèi)大量時(shí)間, 一行一行其實(shí)用不了多少時(shí)間的.
我把全部sql跟pgsql配置都打了個包, 按readme里面的步驟可以執(zhí)行下去, 唐老師有空幫再看下, 不勝感激.
全部查詢.rar (194.58 KB, 下載次數(shù): 24)

2015-06-15 14:10 上傳

點(diǎn)擊文件名下載附件

osdba

我在我的服務(wù)器上試了一下，可以在5分16秒運(yùn)行完：
$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

在我的筆記本電腦上也只運(yùn)行了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

在每個表的大規(guī)模更新后，加上analyze <tablename>;語句。因?yàn)槊看胃�新后馬上執(zhí)行查詢，統(tǒng)計(jì)信息還沒有收集上來，會導(dǎo)致差的執(zhí)行計(jì)劃，所以要手工執(zhí)行analyze <tablename>收集統(tǒng)計(jì)信息，保證后續(xù)的SQL的執(zhí)行計(jì)劃正確。而你分開執(zhí)行快的原因是你等了一會兒，數(shù)據(jù)庫自動幫你收集了統(tǒng)計(jì)信息的原因。我加上analyze <tablename>語句后，在我的筆記本上1分54秒就可以運(yùn)行出來了：
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的問題, 現(xiàn)在56秒跑完
謝謝謝謝...