Subject Re: [firebird-support] Query optimization when using sub query with in operator
Author Svein Erling Tysvær
Post date 2019-03-01T08:43:36Z
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 ivan@... [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 <ivan@...>
------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/firebird-support/join
    (Yahoo! ID required)

<*> To change settings via email:
    firebird-support-digest@yahoogroups.com
    firebird-support-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/