X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 218B753BF3 for ; Sun, 10 Apr 2005 00:26:26 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20877-07 for ; Sat, 9 Apr 2005 23:26:17 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4F53F53B80 for ; Sun, 10 Apr 2005 00:26:16 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j39NQJTc024402; Sat, 9 Apr 2005 19:26:19 -0400 (EDT) To: pankaj naug Cc: pgsql-sql@postgresql.org Subject: Re: subselect query time and loops problem In-reply-to: <20050409202154.71561.qmail@web30507.mail.mud.yahoo.com> References: <20050409202154.71561.qmail@web30507.mail.mud.yahoo.com> Comments: In-reply-to pankaj naug message dated "Sat, 09 Apr 2005 13:21:54 -0700" Date: Sat, 09 Apr 2005 19:26:19 -0400 Message-ID: <24401.1113089179@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/136 X-Sequence-Number: 21029 pankaj naug writes: > Both servers have same version with same postgresql.conf. But the time taken by both the servers with subselect are way different. the loops and the query time are completely different. both servers have same indexes. Evidently one has been analyzed much more recently than the other, because the estimated row counts are wildly different. You didn't say which PG version this is, but I gather that it's pre-7.4, which means that the performance of IN (SELECT ...) is generally going to be awful. Either rewrite as a join or update to 7.4 or later. regards, tom lane