2013/2/19 Bastiaan Olij <bastiaan@xxxxxxxxxxxx>: > 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... try to rewrite OR to two SELECTs joined by UNION ALL Pavel > > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
