Skip site navigation (1) Skip section navigation (2)

Re: statement stuck when the connection grew up to 45 or more

From: kah_hang_ang(at)toray(dot)com(dot)my
To: "Jan Cruz" <malebug(at)gmail(dot)com>,"Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: statement stuck when the connection grew up to 45 or more
Date: 2006-06-09 09:29:08
Message-ID: OF7A6ADED5.FD539DB4-ON48257188.003274C1@pengroup.com.my (view raw or flat)
Thread:
Lists: pgsql-bugs



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.
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"                                                                                                          
                      <malebug(at)gmail(dot)com>         To:       "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>                                       
                      Sent by:                    cc:       pgsql-bugs(at)postgresql(dot)org                                                     
                      pgsql-bugs-owner(at)pos        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



Responses

pgsql-bugs by date

Next:From: Dirk Lutzeb├ĄckDate: 2006-06-09 13:25:55
Subject: How to set the global OID counter? COPY WITH OIDS does not set global OID counter?
Previous:From: Bruce MomjianDate: 2006-06-09 02:40:30
Subject: Re: Fix for Win32 division involving INT_MIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group