On Fri, Jun 09, 2006 at 05:29:08PM +0800, [EMAIL PROTECTED] wrote: > I had check using ps -auxw|grep postgres & using select * from > pg_stat_activity > and it shows that the is really running and use 99% of the CPU. > > Is there any wrong with the query? > As suggested by Jan Cruz, it is to fine tune the performance of the query. > Even if I not fine tune the query it should able to run without problem. > > What I not understand is why this query stuck when the connection growth up > to certain limit. > > I did try execute the query with the connection more than 45 and it really > stuck there. > I wait for around 10 minutes but it still running.
Was the box swapping during this time? What are the specs on the machine? > I kill about 10 connections then the query start to run and finish within > 30 sec. > > May I know what will be the possible cause of this problem? > > Regards, > KH Ang > > > > > > "Jan Cruz" > > <[EMAIL PROTECTED]> To: "Qingqing Zhou" > <[EMAIL PROTECTED]> > Sent by: cc: > pgsql-bugs@postgresql.org > [EMAIL PROTECTED] Subject: Re: [BUGS] statement > stuck when the connection grew up to 45 or more > tgresql.org > > > > > > 06/02/2006 03:08 PM > > > > > > > > > > This is the statement which having problem: > select count(distinct empno) as counter1 from pay_master_history > where empno in (select empno from pay_batch_basic_history where > organizationid like '015003%') > and processyear = '2006' > and processmonth = '05' > and processbatch = '1' > > SELECT COUNT (*) FROM ( > SELECT empno as counter1 from pay_master_history as a > INNER JOIN (select empno from pay_batch_basic_history where organizationid > like '015003%' and processyear = '2006' > and processmonth = '05' > and processbatch = '1') as b > ON b.empno = a.empno ) as count_result > ----------------------------- > or just create the view and use inner join then count :b > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
