Immortal backend .... (kill -9 ?..)

Hi

i have just discovered a query in my database system that would take
extremely long durations to finish. the query plan is like:

explain SELECT source_id , cnt from (SELECT source_id,count(source_id) as
cnt from email_source group by source_id ) subsel join sources
using(source_id) order by source_id
tradein_clients-# ;
NOTICE: QUERY PLAN:
Merge Join (cost=145610.94..147300.41 rows=112596 width=16)
-> Sort (cost=145597.57..145597.57 rows=112596 width=4)
-> Subquery Scan subsel (cost=130520.56..136150.35 rows=112596
width=4) -> Aggregate (cost=130520.56..136150.35 rows=112596 width=4) ->
Group (cost=130520.56..133335.46 rows=1125957 width=4) -> Sort
(cost=130520.56..130520.56 rows=1125957 width=4) -> Seq Scan on
email_source (cost=0.00..17346.57 rows=1125957 width=4) -> Sort
(cost=13.37..13.37 rows=213 width=4)
-> Seq Scan on sources (cost=0.00..5.13 rows=213 width=4)
EXPLAIN

unfortunately it has been running since yesterday and i am
discovering it now.

now even kill -9 <pid of backend> does not releases it below is
the current status of backends:

postgres 8594 0.0 0.0 139416 148 ? S May11 0:25
/usr/local/pgsql/bin/postmaster postgres 8595 1.3 0.1 140408 1040 ?
S May11 78:47 postgres: stats buffer process postgres 8597 1.1 0.0
139704 324 ? S May11 65:18 postgres: stats collector process
postgres 32444 0.0 0.0 238700 0 ? DW May14 0:20 postgres:
tradein tradein_clients 130.94.20.27 SELECT postgres 32482 0.0 0.0 139808
0 ? DW May14 0:00 postgres: checkpoint subprocess postgres 16907
0.0 0.3 140296 3912 ? S 17:51 0:00 postgres: postgres
tradein_clients 203.196.129.235 idle postgres 20382 1.3 4.1 140816 42588 ?
S 18:27 0:01 postgres: tradein tradein_clients 130.94.20.27 idle
in transaction postgres 20388 0.5 6.7 141200 68940 ? S 18:27
0:00 postgres: tradein tradein_clients 130.94.20.27 idle in transaction

The pid of the backend that is executing the query is 32444 .
also note that pid 32482 is also (running?) is since may 14
and 32444 also is (running?) since may 14.
(is there any relation between them?)
what is checkpoint subprocess?

the load average is
[root(at)linux10320 root2]# uptime
6:30pm up 4 days, 6:26, 1 user, load average: 11.08, 10.54, 10.78
[root(at)linux10320 root2]#

can anyone tell me what might me causing so much load in the system,
or someway to get see what is going on to make such a load?

also if i were to bring down postgresql what should have been
a safe (and effective) way.

Disk I/O is like:

# iostat -d 2 6
Linux 2.4.9 (linux10320) 05/15/02

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 32.25 450.01 445.92 166025844 164517760
dev8-1 34.26 434.75 640.21 160395794 236199552

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 2.50 24.00 12.00 48 24
dev8-1 1.00 0.00 12.00 0 24

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 13.00 0.00 112.00 0 224
dev8-1 13.00 0.00 112.00 0 224

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 23.50 0.00 200.00 0 400
dev8-1 23.50 0.00 200.00 0 400

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 3.00 0.00 44.00 0 88
dev8-1 3.00 0.00 44.00 0 88

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
dev8-0 3.00 0.00 36.00 0 72
dev8-1 3.00 0.00 36.00 0 72
[root(at)linux10320 root2]#

Regds
Mallah.

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.