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"
<[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
