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.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
