X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3475A47668A for ; Thu, 15 May 2003 07:57:29 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 88323-09 for ; Thu, 15 May 2003 07:57:27 -0400 (EDT) Received: from mail.deltav.hu (mail.deltav.hu [213.163.0.192]) by postgresql.org (Postfix) with ESMTP id 33E4B4766A1 for ; Thu, 15 May 2003 07:57:27 -0400 (EDT) Received: from fejleszt2 ([213.163.10.103]) by mail.deltav.hu (Netscape Messaging Server 3.6) with SMTP id AAA1A06 for ; Thu, 15 May 2003 13:56:11 +0200 Message-ID: <00a401c31ad8$fa234760$0a03a8c0@fejleszt2> From: =?iso-8859-1?Q?SZUCS_G=E1bor?= To: References: <003c01c31a96$1d1703d0$2e00a8c0@amol> Subject: Re: nested select query failing Date: Thu, 15 May 2003 13:56:10 +0200 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.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/182 X-Sequence-Number: 1961 It's a rather nasty query format, but wrapped it to readable form. Looks like you could make a good join from all these IN's. Another question: does EXPLAIN (without ANALYZE) work for this query? Could you send its output, and table defs? maybe a minimal dump in private email? QUESTION TO PRO'S: Basically, is it true that IN's can be converted to RIGHT JOIN's quite simply? Is it always worth? G. -- while (!asleep()) sheep++; ---------------------------- cut here ------------------------------ ----- Original Message ----- From: "amol" Sent: Thursday, May 15, 2003 5:57 AM > 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 attached_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;