X-Original-To: pgsql-performance@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id BC2A7924C87 for ; Tue, 20 May 2003 05:57:57 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 53409-02 for ; Tue, 20 May 2003 05:57:50 -0400 (EDT) Received: from mithi.com (unknown [219.65.91.97]) by developer.postgresql.org (Postfix) with SMTP id 89297924C89 for ; Tue, 20 May 2003 05:57:00 -0400 (EDT) Received: (qmail 9907 invoked by uid 0); 20 May 2003 07:07:58 -0000 Received: from unknown (HELO amol) (192.168.0.46) by 0 with SMTP; 20 May 2003 07:07:58 -0000 Message-ID: <00a101c31ea1$19e03d80$2e00a8c0@amol> From: "amol" To: References: <20030515075032.B60859-100000@megazone23.bigpanda.com> Subject: Re: nested select query failing Date: Tue, 20 May 2003 12:56:16 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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/188 X-Sequence-Number: 1967 thanks allot everybody for your mails, - It helped and now I have got down the query execution time allot. But I am facing problem in following query ----------- explain analyze select attached_info.id from attached_tag_list, attached_info where attached_tag_list.attached_tag = 265 and attached_tag_list.id = attached_info.id ---------- - it's result is ---------- NOTICE: QUERY PLAN: Nested Loop (cost=0.00..165349.50 rows=114 width=16) (actual time=117.14..8994.60 rows=15 loops=1) -> Index Scan using ix_attached_tag_list_id on attached_tag_list (cost=0.00..111.13 rows=96 width=12) (actual time=0.12..0.66 rows=15 loops=1) -> Seq Scan on attached_info (cost=0.00..1211.53 rows=33553 width=4) (actual time=3.67..197.98 rows=33553 loops=15) Total runtime: 8994.92 msec EXPLAIN --------- - I have already indexed attached_info on id using following query ------ CREATE INDEX attached_info_Index_1 ON attached_info(id) ; ------ - But I am wondering why there is "->Seq Scan on attached_info." After reading various documentation on the internet I am assuming it should have been an index scan. BTW I have done vaccume analyze also. Am I right? thanks, Amol ----- Original Message ----- From: "Stephan Szabo" To: "SZUCS Gábor" Cc: Sent: Thursday, May 15, 2003 8:26 PM Subject: Re: [PERFORM] nested select query failing On Thu, 15 May 2003, [iso-8859-1] SZUCS Gábor wrote: > Basically, is it true that IN's can be converted to RIGHT JOIN's quite > simply? Is it always worth? I'm not sure you want to convert to an outer join (since you want to throw away the rows on either side that don't match in an IN). You also have to be careful not to get duplicate entries from what was the subquery. As for whether it's worth doing, in 7.3 and earlier, almost certainly, in 7.4 almost certainly not. :) ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster