nested select query failing - Mailing list pgsql-performance

Hi everybody,
I am new to this mailing list, so please let me know if I am not posting
queries the way you are expecting.

-  We are porting a web based application from MSSQL to postgres as a
backend.
This is a database intensive application. I am facing a problem in some
queries like this :

select distinct attached_info.id, ownerid ,attached_info.modified_date from
attached_info where attached_info.id in (  select distinct
attached_tag_list.id from attached_tag_list where attached_tag_list.id in
 select attached_info.id from attached_info where
ttached_info.deleted='0'  )  and attached_tag_list.id  in ( select id from
attached_tag_list where attached_tag = 262 )  and
attached_tag_list.attached_tag in ( select tags.id from tags where tags.id
in ( select tag_id from tag_classifier, tag_classifier_association where
classifier_tag_id in ( 261, 4467, 1894, 1045, 1087, 1355, 72, 1786, 1179,
3090, 871, 3571, 3565, 3569, 3567, 1043, 2535, 1080, 3315, 87, 1041, 2343,
2345, 1869, 3088, 3872, 2651, 2923, 2302, 1681, 3636, 3964, 2778, 2694,
1371, 2532, 2527, 3742, 3740, 1761, 4530, 4671, 4503, 4512, 3700 ) and
association_id='1566' and
tag_classifier.uid=tag_classifier_association.uid )  and
tags.isdeleted='0' )  )  order by attached_info.modified_date desc,
attached_info.id desc;

When I fire this query in psql, it does not return back.

- top command shows postgres above 95+% cpu usage consistantly
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
 1550 postgres  25   0 20268  19M 18904 R    95.3  5.2   6:31 postmaster

- I am using RedHat 8 with following postgres rpms
postgresql-libs-7.2.2-1
postgresql-7.2.2-1
postgresql-server-7.2.2-1

- RAM size is 384 MB, SWAP size is 384 MB, but top shows that memory is free

- I have done following changes after searching for performance realted
information on the internet and postgres site
            - in /etc/rc.d/rc.local added following lines
                            echo "32768" >/proc/sys/fs/file-max
                            echo "98304" >/proc/sys/fs/inode-max
            - in /etc/init.d/postgresql file a pg_ctl call is changed to :
                su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -o
'-i -N 1024 -B 2048 -d 5' -p /usr/bin/postmaster start  >>
/var/log/pgsql.log 2>&1" < /dev/null

- pgsql log shows :
......
}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:keycount 3 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:numCols 3 :uniqColIdx 3 1 2 }
DEBUG:  ProcessQuery
*********
log stops here for 5/6 minuts
*********
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 1595) exited with exit code 0
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 1598) exited with exit code 0
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 1599) exited with exit code 0
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  child process (pid 1600) exited with exit code 0


- What should I do to get such queries working?
- Is there any limit on query size?
- Is there anything left in tuning postgres which is causing this problem ?

If you want me to try anything, please let me know.

thanks
Amol