High selectivity of index, but a lot of nulls

[Tomasz Dubiel]

Hello.

When querying a table and filtering with a column  (where column is null) which has an index on it with excellent selectivity, but still major part of values is NULL, is there a common solution for it with Firebird 5?

On Firebird 3 we can disable using such index in query (+0), but on one database with a lot of nulls in this column it will work, but on another we can screw perfomance with it.

Will partial indexes introduced in Firebird 5 help with it? We need a global solution which will work in almost all databases with various distribution of values in the index.

Best regards,

Tomek.

[Mark Rotteveel]

On 30/07/2025 13:42, Tomasz Dubiel wrote:
> When querying a table and filtering with a column  (*where column is
> null*) which has an index on it with excellent selectivity, but still

> major part of values is NULL, is there a common solution for it with
> Firebird 5?
> On Firebird 3 we can disable using such index in query (+0), but on one
> database with a lot of nulls in this column it will work, but on another
> we can screw perfomance with it.
> Will partial indexes introduced in Firebird 5 help with it? We need a
> global solution which will work in almost all databases with various
> distribution of values in the index.

This really depends on the actual queries involved, and the actual
problem you're trying to solve.

As presented (a column with a lot of NULL values, and selecting `where
column is null`), then I would _guess_ no, a partial index will probably
not help you, unless you want to prevent use of the index _if_ you're
querying `where column is null` (by having partial index on column with
condition `column is not null`), though I'm not sure if the natural scan
that would cause will help improve performance.

If you have a query with `where column1 = ... and column2 is null`, then
having a partial index on column1 with condition `where column2 is null`
might improve performance.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 30.07.2025 13:49:
>
> If you have a query with `where column1 = ... and column2 is null`, then having
> a partial index on column1 with condition `where column2 is null` might improve
> performance.

Is it allowed to have references to other columns in a partial index condition?

--
WBR, SD.

[Mark Rotteveel]

Yes. That is the primary use-case of partial index.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 30.07.2025 13:55:
>>    Is it allowed to have references to other columns in a partial index
>> condition?
>
> Yes. That is the primary use-case of partial index.

Documentation doesn't mention this possibility.
What happen if a record is updated in a such way that the indexed column is
not changed but the condition based on other column is? Firebird code used to
modify indexes only on changes in the indexed field(s).

--
WBR, SD.

[Mark Rotteveel]

Yes it does. I guess you've looked at the examples in the release notes,
and not the language reference. See the last example at
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-ddl-idx-crtidxexmpls

Mark
--
Mark Rotteveel

[Mark Rotteveel]

And to answer your second question based on the example I linked in my
previous message:

SQL> create table OFFER (
CON> OFFER_ID bigint generated always as identity primary key,
CON> PRODUCT_ID bigint not null,
CON> ARCHIVED boolean default false not null,
CON> PRICE decimal(9,2) not null
CON> );
SQL> create unique index IDX_OFFER_UNIQUE_PRODUCT
CON> on OFFER (PRODUCT_ID)
CON> where not ARCHIVED;
SQL> insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, false,
18.95);
SQL> insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true,
17.95);
SQL> insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true,
16.95);
SQL> update offer set archived = false;
Statement failed, SQLSTATE = 23000
attempt to store duplicate value (visible to active transactions) in
unique index "IDX_OFFER_UNIQUE_PRODUCT"
-Problematic key value is ("PRODUCT_ID" = 1)
SQL>

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 30.07.2025 14:09:
> Yes it does. I guess you've looked at the examples in the release notes, and not
> the language reference.

I looked on the main description in the Language reference but examples from
corresponding README only. My bad.

--
WBR, SD.

[Tomasz Dubiel]

Thank you for your answers. After posting, I reanalysed my problem and found other solution, but anyway, your informations are always helpful.

Best regards,

Tomek.