WHERE ID IN ( <constants> ) is very different from WHERE ID IN ( <subselect> ). In your simple case the subselect is not correlated and only needs to be executed once, but correlated queries are more complex. What I mean by correlated can be seen in this example:
SELECT ... FROM A WHERE A.ID IN ( SELECT B.A_ID FROM B WHERE B.AField = A.AField ) In the above query, the subselect has to be executed once for every possible row in A and hence, no index can be useful for A. Sure, it would be good if the optimizer to a greater extent had understood the difference between correlated and not correlated subqueries, but it doesn't. Normally, it is easy to fix IN ( <subselect> ) queries by changing to constants or EXISTS (sometimes even a simple JOIN), though I do admit that I sometimes find it a bit annoying when I find the following query to be time consuming (when A contains many millions of rows and B only a handful) UPDATE A SET A.Field = ( SELECT B.Field FROM B WHERE A.ID = B.A_ID ) WHERE EXISTS( SELECT * FROM B WHERE A.ID = B.A_ID ) and have to rewrite it to EXECUTE BLOCK AS DECLARE VARIABLE ID Integer; DECLARE VARIABLE MyField Varchar( 200 ); BEGIN FOR SELECT A_ID, FIELD FROM B INTO :ID, :MyField DO BEGIN UPDATE A SET FIELD = :MyField WHERE ID = :ID; END END Note that I'm still on Firebird 2.5 and don't know if 3.0 is better with such queries or not. HTH, Set Den fre. 1. mar. 2019 kl. 01:11 skrev Ivan C Cruz i...@supplymidia.com.br [firebird-support] <firebird-support@yahoogroups.com>: > The following problem applies to Firebird 3.0. Cant test right now on > older versions. > > Suppose we have a table called AnyTable with column ID as primary key. > > The query > > select * from AnyTable where ID = '123' > > produce the following plan... > > PLAN (AnyTable INDEX (PK_AnyTable)) > > Now a second query, semantically identical to the first... > > select * from AnyTable where ID in ( '123' ) > > will produce the same plan: > > PLAN (AnyTable INDEX (PK_AnyTable)) > > However that third query, also semantically identical to the first... > > select * from AnyTable where ID in ( select '123' from RDB$DATABASE ) > > will produce a really bad plan: > > PLAN (RDB$DATABASE NATURAL) > PLAN (AnyTable NATURAL) > > If I try to force the primary key index, an error occurs: > > SQL Message : -284 > index cannot be used in the specified plan > > Are there any way to force index usage on a situation like that? > > Ivan Cruz. > > > ------------------------------------ > Posted by: Ivan C Cruz <i...@supplymidia.com.br> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > > >