Re: Re: [GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help - Mailing list pgsql-general

From 657985552@qq.com
Subject Re: Re: [GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help
Date
Msg-id 2015102816561339199063@qq.com
Whole thread Raw
In response to 回复: postgres cpu 100% need help  ("657985552@qq.com" <657985552@qq.com>)
List pgsql-general
  I can change the sql . Needed a   little business modify in my application  .but i don't know why  in high load time  I Executing the sql
,it cost  200-300ms . but the log record it cost 2000ms

log message:
 duration: 2042.493 ms  execute <unnamed>:           select o_count from tshow.p_hpart_liveingroomlist_count($1)","parameters: $1 = '46'

thanks 


657985552@qq.com
 
发件人: 657985552@qq.com
发送时间: 2015-10-28 16:53
主题: Re: Re: [GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help
thanks ,  I can change the sql . Needed a   little business modify in my application  .but i don't know why  in high load time  I Executing the sql
,it cost  200-300ms . but the log record it cost 2000ms

log message:
 duration: 2042.493 ms  execute <unnamed>:           select o_count from tshow.p_hpart_liveingroomlist_count($1)","parameters: $1 = '46'


657985552@qq.com
 
发件人: John R Pierce
发送时间: 2015-10-28 16:28
收件人: pgsql-general
主题: Re: [GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help
On 10/28/2015 12:12 AM, 657985552@qq.com wrote:
Thank you for your reply.
tshow=> explain (analyze, buffers)  select count(t.*)  from tshow.res_room_weight t,tshow.res_room_info r 
tshow->   where t.subcatlg_id=46 
tshow->   and t.roomid = r.actorid
tshow->   and r.levels>=0;

http://explain.depesz.com/s/oKN5

shows where the time is spent in that query, but as you say, that one the timing was fine.    there's a lot of time being spent in a seq_scan of res_room_info.

I would have written that query as the inner join it is, but that probably won't effect how it's executed.

select ... from tshow.res_room_weight t join tshow.res_room_info r on (t.roomid = r.actorid) where  t.subcatlg_id=46 and r.levels>=0;


-- 
john r pierce, recycling bits in santa cruz

pgsql-general by date:

Previous
From: "657985552@qq.com"
Date:
Subject: Re: Re: [GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help
Next
From: Mark Morgan Lloyd
Date:
Subject: Re: Red Hat Policies Regarding PostgreSQL