X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 88539476549 for ; Wed, 14 May 2003 23:49:05 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 01928-05 for ; Wed, 14 May 2003 23:49:04 -0400 (EDT) Received: from shield.nordlb.lv (shield.pirmabanka.lv [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id 62FD8475CBC for ; Wed, 14 May 2003 23:49:03 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.nordlb.lv [195.114.57.14]) by shield.nordlb.lv (8.12.9/8.12.9) with SMTP id h4F3n7bg019654 for ; Thu, 15 May 2003 06:49:07 +0300 Received: from share.nordlb.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Thu, 15 May 2003 06:49:06 +0300 (EET DST) Received: from pc311x1.nordlb.lv (localhost.localdomain [127.0.0.1]) by nordlb.lv (8.11.6/8.11.6) with SMTP id h4F3n6Y25243 for ; Thu, 15 May 2003 06:49:06 +0300 Received: (qmail 3216 invoked by uid 100); 15 May 2003 03:51:46 -0000 Date: Thu, 15 May 2003 06:51:46 +0300 From: "Victor Yegorov" To: "amol" Cc: Subject: Re: nested select query failing Message-ID: <20030515035146.GH1549@nordlb.lv> References: <003c01c31a96$1d1703d0$2e00a8c0@amol> Mime-Version: 1.0 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline In-Reply-To: <003c01c31a96$1d1703d0$2e00a8c0@amol> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/179 X-Sequence-Number: 1958 * amol [15.05.2003 06:47]: > 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; IN () constructs isn't a good part of postgres (from the performance point of view). Try to rewrite your query using joins or EXISTS/NOT EXISTS constructs. Search archives for more details, there were a discussion of this topic lately. -- Victor Yegorov