Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 19677476E74 for ; Fri, 20 Sep 2002 14:40:53 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id A57FB476E6E for ; Fri, 20 Sep 2002 14:40:52 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 231C738193D; Fri, 20 Sep 2002 14:38:54 -0400 (EDT) From: CoL X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Two Permance Questions Date: Fri, 20 Sep 2002 20:42:50 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 27 Message-ID: References: <20020920020711.32693.qmail@graffiti.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: news@hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2a) Gecko/20020910 X-Accept-Language: en-us, en To: pgsql-sql@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/356 X-Sequence-Number: 9633 The select without subselect of course is better, and faster. Check your "explain analyze select ..." to se the performance, and to check the index usage if any. C. CN LIOU wrote: > Hi! > > Q1. Is subquery better or join? > > For subquery: > > SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1 > > I wonder it will loop n times if t1 returns n rows. If this is the case, is it better to use join like this: > > SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1 > > Q2. If the query is not optimize like this: > > SELECT t1.c1,t2.c2 FROM t1,t2,t1,t2,t2 WHERE t2.c1=t1.c1 AND t1.c1=t2.c1 AND t1.c1=t2.c1 > > and the size of this clause can soar up to several kbytes, then can this query cause performance problem? > > Regards,