On 17/06/2020 14:56, Dmitry Yemanov wrote: > 17.06.2020 19:42, Adriano dos Santos Fernandes wrote: >> >>> Unfortunately, not exactly so. ConditionalStream is good only when >>> choice is between NATURAL and INDEX plans. It cannot be used when >>> choosing between good and bad index matches. >>> >>> Currently, your PR shows a regression for CORE-5236, subquery uses >>> another index (single-segment) while it used both segments of the >>> composite index before. I.e. injected ORs prevent good index matching. >> >> Looking at fbt test I see the difference, but that difference happens in >> the place when we look for a single record (when we have a null value we >> must known only if the stream has any record). >> >> If so, it should not matter, or not matter a lot. >> >> Or is it just the small part of the more general problem? > > The difference is in the sub-query plan, there's no ConditionalStream > there. So it doesn't make any difference whether <value> is NULL or > not, in both cases index for COLUMN = VALUE is simply not used thus > returning the same result set for the every input record (no > index-based correlation). > > -- original boolean > COLUMN1 = VALUE1 -- using index for COLUMN1 > -- injected boolean > AND COLUMN2 = VALUE2 -- not using index for COLUMN2 > OR COLUMN IS NULL > OR VALUE IS NULL > > This happens because we already have an inversion for COLUMN1 and > ConditionalStream is not created in this case. > Ok.
So it's about this, correct? -- Uses two index segments select registro_sec_roest from dp_registro_oest where registro_pri_roest = 1 and registro_sec_roest = 1; -- Uses one index segment - more generalization of IN problem select registro_sec_roest from dp_registro_oest where registro_pri_roest = 1 and registro_sec_roest in (1, 2); -- Uses one index segment - IN problem select registro_sec_roest from dp_registro_oest where registro_pri_roest = 1 and (registro_sec_roest = 1 or registro_sec_roest is null); Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
