X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B008D4761C0 for ; Thu, 15 May 2003 00:24:09 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 03212-10 for ; Thu, 15 May 2003 00:23:57 -0400 (EDT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by postgresql.org (Postfix) with ESMTP id 21903476085 for ; Thu, 15 May 2003 00:23:56 -0400 (EDT) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6p2/8.11.6) id h4F4NxD12730 for pgsql-performance@postgresql.org; Thu, 15 May 2003 12:23:59 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6p2/8.9.3) with SMTP id h4F4NrJ12640; Thu, 15 May 2003 12:23:53 +0800 (WST) Message-ID: <063501c31a99$d4425020$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "amol" , References: <003c01c31a96$1d1703d0$2e00a8c0@amol> Subject: Re: nested select query failing Date: Thu, 15 May 2003 12:24:08 +0800 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 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/180 X-Sequence-Number: 1959 Please post the EXPLAIN ANALYZE of that query... Chris ----- Original Message ----- From: "amol" To: Sent: Thursday, May 15, 2003 11:57 AM Subject: [PERFORM] nested select query failing > 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 > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >