X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4E7FF476549 for ; Wed, 14 May 2003 23:41:54 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 01167-10 for ; Wed, 14 May 2003 23:41:52 -0400 (EDT) Received: from mithi.com (unknown [219.65.91.97]) by postgresql.org (Postfix) with SMTP id E816F476555 for ; Wed, 14 May 2003 23:41:50 -0400 (EDT) Received: (qmail 29272 invoked by uid 0); 15 May 2003 03:38:45 -0000 Received: from unknown (HELO amol) (192.168.0.46) by 0 with SMTP; 15 May 2003 03:38:45 -0000 Message-ID: <003c01c31a96$1d1703d0$2e00a8c0@amol> From: "amol" To: Subject: nested select query failing Date: Thu, 15 May 2003 09:27:33 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/178 X-Sequence-Number: 1957 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