Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7hl4-0006uB-Gw for pgsql-performance@arkaria.postgresql.org; Tue, 19 Feb 2013 07:36:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U7hl3-0002wE-Tk for pgsql-performance@arkaria.postgresql.org; Tue, 19 Feb 2013 07:36:57 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7hl3-0002w8-2J for pgsql-performance@postgresql.org; Tue, 19 Feb 2013 07:36:57 +0000 Received: from p3plsmtpa06-10.prod.phx3.secureserver.net ([173.201.192.111]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7hkx-0004Wu-3w for pgsql-performance@postgresql.org; Tue, 19 Feb 2013 07:36:55 +0000 Received: from Bass-MacBook-Retina.local ([59.167.175.241]) by p3plsmtpa06-10.prod.phx3.secureserver.net with id 27co1l00B5CsK4T017cp3b; Tue, 19 Feb 2013 00:36:49 -0700 Message-ID: <51232B90.2080504@basenlily.me> Date: Tue, 19 Feb 2013 18:36:48 +1100 From: Bastiaan Olij User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:17.0) Gecko/20130107 Thunderbird/17.0.2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Speed of exist References: <51231D10.1070101@basenlily.me> <51232A36.6000701@orprovision.com> In-Reply-To: <51232A36.6000701@orprovision.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Hi Andy, I've tried that with the same result. One subquery works beautifully, two subqueries with an OR and it starts to do a sequential scan... Thanks, Bastiaan Olij On 19/02/13 6:31 PM, Andy wrote: > Limit the sub-queries to 1, i.e. : > > select 1 from Table2 where Table2.ForeignKey = Table1.PrimaryKey fetch > first 1 rows only > > Andy. > > On 19.02.2013 07:34, Bastiaan Olij wrote: >> Hi All, >> >> Hope someone can help me a little bit here: >> >> I've got a query like the following: >> -- >> select Column1, Column2, Column3 >> from Table1 >> where exists (select 1 from Table2 where Table2.ForeignKey = >> Table1.PrimaryKey) >> or exists (select 1 from Table3 where Table3.ForeignKey = >> Table1.PrimaryKey) >> -- >> >> Looking at the query plan it is doing a sequential scan on both Table2 >> and Table3. >> >> If I remove one of the subqueries and turn the query into: >> -- >> select Column1, Column2, Column3 >> from Table1 >> where exists (select 1 from Table2 where Table2.ForeignKey = >> Table1.PrimaryKey) >> -- >> >> It is nicely doing an index scan on the index that is on >> Table2.ForeignKey. >> >> As Table2 and Table3 are rather large the first query takes minutes >> while the second query takes 18ms. >> >> Is there a way to speed this up or an alternative way of selecting >> records from Table1 which have related records in Table2 or Table3 which >> is faster? >> >> Kindest Regards, >> >> Bastiaan Olij >> >> >> > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance