Question

[Tomasz Dubiel]

Hello. Firebird 3.0.14:

Let have such a query:

select d.id_dysp,
from dyspsprz ds
inner join dysp d on d.id_dysp = ds.id_dysp
inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
inner join magazyn m on m.id_magazyn = d.id_magazyn
inner join defdok dd on dd.id_defdok = d.id_defdok
where ds.id_dysp2 = 108848

This doesn't work very well, because for ID_DYSP2 all values are null, a lot of indexed reads from DYSPSPRZ:

PLAN JOIN (ST NATURAL, D INDEX (FK_DYSP_STATUSDYSP), M INDEX (PK_MAGAZYN), DD INDEX (PK_DEFDOK), DS INDEX (FK_DYSPSPRZ_DYSP))

It's enough to rewrite it to:

select d.id_dysp
from dyspsprz ds
inner join dysp d on d.id_dysp = ds.id_dysp
inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
inner join magazyn m on m.id_magazyn = d.id_magazyn
inner join defdok dd on dd.id_defdok = d.id_defdok
where ds.id_dysp2 = 108848 and ds.id_dysp2 is not null

and then the results come instantly, there are completely no reads and plan:

PLAN JOIN (DS INDEX (FK_DYSPSPRZ_DYSP2), D INDEX (PK_DYSP), ST INDEX (PK_STATUSDYSP), M INDEX (PK_MAGAZYN), DD INDEX (PK_DEFDOK))

Question:

given the fact that first condition already stated that I want fixed value distinct from null, couldn't Firebird behave like in the second case, without explicitly specyfing condition IS NOT NULL?

ID_DYSP2 has the worst possible selectivity = 1. Is it necessary to provide an additional condition in such cases or is there a place for Firebird for improvement?

I understand that for first query optimizer ignored index for ID_DYSP2 because of the bad selectivity, but for not null there are some exceptions?

Best regards,

Tomek.

[Mark Rotteveel]

On 14-05-2026 15:34, Tomasz Dubiel wrote:
> Hello. Firebird 3.0.14:
> Let have such a query:

> /select d.id_dysp,

> from dyspsprz ds
> inner join dysp d on d.id_dysp = ds.id_dysp
> inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
> inner join magazyn m on m.id_magazyn = d.id_magazyn
> inner join defdok dd on dd.id_defdok = d.id_defdok

> where ds.id_dysp2 = 108848/

> This doesn't work very well, because for ID_DYSP2 all values are null, a
> lot of indexed reads from DYSPSPRZ:

> /PLAN JOIN (ST NATURAL, D INDEX (FK_DYSP_STATUSDYSP), M INDEX
> (PK_MAGAZYN), DD INDEX (PK_DEFDOK), DS INDEX (FK_DYSPSPRZ_DYSP))/

> It's enough to rewrite it to:

> /select d.id_dysp

> from dyspsprz ds
> inner join dysp d on d.id_dysp = ds.id_dysp
> inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
> inner join magazyn m on m.id_magazyn = d.id_magazyn
> inner join defdok dd on dd.id_defdok = d.id_defdok

> where ds.id_dysp2 = 108848 *and ds.id_dysp2 is not null*/

> and then the results come instantly, there are completely no reads and plan:

> /PLAN JOIN (DS INDEX (FK_DYSPSPRZ_DYSP2), D INDEX (PK_DYSP), ST INDEX
> (PK_STATUSDYSP), M INDEX (PK_MAGAZYN), DD INDEX (PK_DEFDOK))/

> Question:

> given the fact that first condition already stated that I want fixed
> value distinct from null, couldn't Firebird behave like in the second
> case, without explicitly specyfing condition IS NOT NULL?
> ID_DYSP2 has the worst possible selectivity = 1. Is it necessary to
> provide an additional condition in such cases or is there a place for
> Firebird for improvement?
> I understand that for first query optimizer ignored index for ID_DYSP2
> because of the bad selectivity, but for not null there are some exceptions?

I don't think the reason here is the addition of `is not null`, but the
addition of a conjunct involving ds.id_dysp2. I guess that having two
ANDed conditions on the same column makes it score higher for the
optimiser (but I haven't scrutinised the optimisers code, so this a
*guess*), thus making it select the index with a bad selectivity anyway.

In other words, I suspect that any additional condition involving
ds.id_dysp2 would have this effect. Like, for example:

where ds.id_dysp2 = 108848 and ds.id_dysp2 <> 0

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

You're right. This query used the index:

select d.id_dysp
from dyspsprz ds
inner join dysp d on d.id_dysp = ds.id_dysp
inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
inner join magazyn m on m.id_magazyn = d.id_magazyn
inner join defdok dd on dd.id_defdok = d.id_defdok

where ds.id_dysp2 = 108848 and ds.id_dysp2 is not null

but the one below didn't:

select d.id_dysp
from dyspsprz ds
inner join dysp d on d.id_dysp = ds.id_dysp
inner join statusdysp st on st.id_statusdysp = d.id_statusdysp
inner join magazyn m on m.id_magazyn = d.id_magazyn
inner join defdok dd on dd.id_defdok = d.id_defdok

where ds.id_dysp2 <>0 and ds.id_dysp2 is not null

Best regards, Tomek.

[Svein Erling Tysvær]

I'm just curious and have no clue whether the optimizer in Firebird 3 removes duplicates in the where clause, but does (the logically senseless)

where ds.id_dysp2 = 108848 and ds.id_dysp2 = 108848

use the good or bad plan? It is just that equal and not equal are very different, so <> 0 is a very different query.

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/d217d9b7-9331-4237-bc1f-8b5c8c5c4b3en%40googlegroups.com.

[Tomasz Dubiel]

It removes duplicates. It behaves like with only one condition.