Performance problem - gfix reports no error, index selectivities up to date, sweep performed

[Tomasz Dubiel]

Hello.

Firebird 3.0.10. I have a performance problem. I run query and it runs for x time with some plan and many indexed reads.

I update indexes selectivities, there are no pending transactions and even no connections apart from me. I recompiled all procedures and triggers. I run even sweep. 

fpw     Thu May  7 10:53:51 2026
        Sweep is started by SYSDBA
        OIT 319164234, OAT 319164235, OST 319164235, Next 319164235

fpw     Thu May  7 10:53:53 2026
        Sweep is finished
        OIT 319164235, OAT 319164235, OST 319164235, Next 319164236

I run gfix -v -fu. It only reports warnings about orphan pages and "relation has orphan backversions". Everything is ok, but still performance is not good.

I perform backup restore and voila, query runs much faster, with different plan and much less indexed reads. And what is even more interesting - the database before was 49 GB and after restore 52 GB.

Did I miss something? Forgot about something? What can be the reason of such situation?

Best regards,

Tomek.

[Attila Molnár]

Deactivated index maybe?

[Tomasz Dubiel]

SELECT RDB$INDEX_NAME, RDB$INDEX_INACTIVE FROM RDB$INDICES WHERE RDB$INDEX_INACTIVE <> 0;

It reports nothing

[Mark Rotteveel]

You're 4 point releases behind. Have you tried with 3.0.14? Especially
given the security issues fixed in 3.0.14, you really need to upgrade.

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

We are expected to upgrade to the newest 5.0. However, I ask for possible reasons. Is this some support issue, or Firebird error issue? No errors from gfix are expected?

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 07.05.2026 11:48:
> However, I ask for possible reasons.

Look at the difference in plans. Compare statistics for used/not used
indexes, tables and all around.

Performance analyze is a complex task requiring knowledge and a lot of data,
it is not something that can be done using a crystal ball in a support mail-list.

--
WBR, SD.

[Tomasz Dubiel]

All indexes are active, I recomputed selectivities for all of them, gfix reported no errors. To my knowledge this should fix any problems with indexes and their selectivities.

"Performance analyze is a complex task requiring knowledge and a lot of data" - that's why I ask what I could miss in this analysis.

Thanks.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 07.05.2026 12:08:
> All indexes are active, I recomputed selectivities for all of them, gfix
> reported no errors. To my knowledge this should fix any problems with indexes
> and their selectivities.

Unless your queries were prepared before index recalculation was finished.
Did you check values in system tables and gstat output before and after?

> "Performance analyze is a complex task requiring knowledge and a lot of data" -
> that's why I ask what I could miss in this analysis.

Number of visible records and volume of garbage, page filling factor and
record fragmentation. These the first things that comes to mind, though they
hardly explain drastic changes in the query plan.
That's why I said "analyze _everything_ around involved tables".

--
WBR, SD.

[Mark Rotteveel]

On 07-05-2026 11:48, Tomasz Dubiel wrote:
> We are expected to upgrade to the newest 5.0. However, I ask for
> possible reasons. Is this some support issue, or Firebird error issue?
> No errors from gfix are expected?

Gfix can only fix a limited set of database corruption and perform
sweep. Performance issues are usually not related to corruption
(corruption more likely results in errors than just a slowdown), and
given a sweep was not needed (given the stats shown), and thus wouldn't
have an impact, gfix would not help.

Though that you have some orphan pages and orphaned records is not
immediately concerning, in practice orphans should be rare (maybe even
non-existent), unless Firebird or your OS had a hard crash or hard
shutdown (and several Firebird crash causes were fixed after 3.0.10!).

Or maybe data was very fragmented in your database (i.e. lots of records
split over multiple pages that are not near each other), which might be
why the backup and restore fixed it.

It is also possible that you might have some index issue that is not
detected by gfix, but that would probably need gstat index stats before
and after restore than gfix output, or an attempt to `alter index XXX
active` for the indexes of the tables involved in the slow queries to
see if that fixes things (`alter index XXX active` will also rebuild an
already active index).

The size change you mention is also not unexpected: in a fresh database,
all data pages reserve free space for record updates, in a database that
is in active use, such space will eventually get used for records,
making the data pages denser than in a freshly restored database.

And as an aside, even if you're planning on upgrading on 5.0, it is a
very bad idea to not update Firebird for nearly 4 years (3.0.10 was
released in June 2022). And especially with the security issues
addressed in 3.0.14/4.0.7/5.0.4 (and previously one in
3.0.13/4.0.6/5.0.3), you should update sooner rather than later.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 07-05-2026 12:08, Tomasz Dubiel wrote:
> All indexes are active, I recomputed selectivities for all of them, gfix
> reported no errors. To my knowledge this should fix any problems with
> indexes and their selectivities.

Recomputing selectivities won't rebuild your index, so won't fix
possible issues with the index data itself. For that you need to use
`alter index XXX active`.

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

The problem is with query:

select KT.ID_KONTRAH, KT.ID_SLOWNIK, KT.NRKONTRAH, KT.NAZWASKR, KT.TERMINDNI, KT.PROCRABATNAGL, KT.ID_AKWIZYTORDEF,
       KT.DOKZEW, KT.ID_ZRODLO_DANYCH_WRA, DK.ID_KONTRAH as ID, DK.ID_DANEKONTRAH, DK.NAZWADL, DK.NIP, DK.NIPW,
       DK.MIEJSCOWOSC, DK.ULICA, DK.ID_KRAJ, DK.EMAILEFAKTURA, PL.NAZWASKR as PLATNIK, AK.NAZWISKOIMIE as AKWIZYTOR,
       GK1.KODZLOZONY as GKK1, GK10001.KODZLOZONY as GKK10001, GK10002.KODZLOZONY as GKK10002,
       GK10002.NAZWAZLOZONA as GKN10002, KP1.ID_KONTRAH_PRIORYTET, KP1.KOLOR, OZN.NR_OZNACZDOK
from KONTRAH KT
inner join WYSTGRKTR WGKF on (KT.ID_KONTRAH = WGKF.ID_KONTRAH) and
      (WGKF.ID_RODZGRUPKTR = 10001)
inner join ZEST_GRUPAKONTRAH_LISTA ZGKL on (WGKF.ID_GRUPAKONTRAH = ZGKL.ID_GRUPAKONTRAHNAL) and
      (ZGKL.ID_GRUPAKONTRAH = 10012)
inner join KONTRAH_PRIORYTET KP1 on (KT.ID_KONTRAH_PRIORYTET = KP1.ID_KONTRAH_PRIORYTET)
left outer join DANEKONTRAH DK on (KT.ID_KONTRAH = DK.ID_KONTRAH) and
      (DK.BAZADANEKONTRAH = 1)
left outer join KONTRAH PL on (DK.ID_PLATNIKA = PL.ID_KONTRAH)
left outer join AKWIZYTOR AK on (KT.ID_AKWIZYTORDEF = AK.ID_AKWIZYTOR)
left outer join WYSTGRKTR WG1 on (KT.ID_KONTRAH = WG1.ID_KONTRAH) and
      (WG1.ID_RODZGRUPKTR = 1)
left outer join GRUPAKONTRAH GK1 on (WG1.ID_GRUPAKONTRAH = GK1.ID_GRUPAKONTRAH)
left outer join WYSTGRKTR WG10001 on (KT.ID_KONTRAH = WG10001.ID_KONTRAH) and
      (WG10001.ID_RODZGRUPKTR = 10001)
left outer join GRUPAKONTRAH GK10001 on (WG10001.ID_GRUPAKONTRAH = GK10001.ID_GRUPAKONTRAH)
left outer join WYSTGRKTR WG10002 on (KT.ID_KONTRAH = WG10002.ID_KONTRAH) and
      (WG10002.ID_RODZGRUPKTR = 10002)
left outer join GRUPAKONTRAH GK10002 on (WG10002.ID_GRUPAKONTRAH = GK10002.ID_GRUPAKONTRAH)
left outer join OZNACZDOK OZN on (KT.ID_KONTRAH = OZN.ID_VALUE) and
      (OZN.ID_UZYTKOWNIK = 12) and
      (OZN.RODZAJ = 10)
where (KT.NIEHANDLOWYW = 0) and
      ((KT.BAZAKONTRAH = 0))

what I tried:

rebuilding/dropping and recreating indexes: FK_WYSTGRKT_GRKTRWYST_GRUPAKON, FK_WYSTGRKT_RGRKTRWYS_RODZGRUP, FK_KONTRAH_SLOWNIKRODZAJ, MK_ZEST_GRUPAKONTRAH_LISTA_G, PK_ZEST_GRUPAKONTRAH_LISTA

nothing changed

When I recreated KONTRAH as KONTRAH_NEW and adjusted query - nothing changed.

When I recreated WYSTGRKTR as WYSTGRKTR_NEW and adjusted query, query finally started working like on the database after restore.

Could you tell me please what statistics below can tell me and what I can do to fix data in WYSTGRKTR and avoid backup restore? Sorry for probably too many data.

Best regards,

Tomek.

gstat on old database:
WYSTGRKTR (392)
    Primary pointer page: 1447, Index root page: 1448
    Total formats: 1, used formats: 1
    Average record length: 16.66, total records: 628985
    Average version length: 0.00, total versions: 0, max versions: 0
    Average fragment length: 0.00, total fragments: 0, max fragments: 0
    Average unpacked length: 16.00, compression ratio: 0.96
    Pointer pages: 1, data page slots: 2232
    Data pages: 2232, average fill: 58%
    Primary pages: 2232, secondary pages: 0, swept pages: 2231
    Empty pages: 1, full pages: 2230
    Fill distribution:
         0 - 19% = 1
        20 - 39% = 1
        40 - 59% = 2230
        60 - 79% = 0
        80 - 99% = 0

FK_WYSTGRKT_GRKTRWYST_GRUPAKON  

depth: 2
leaf buckets: 340
nodes: 629026

total dup: 628960
max dup: 208874

Clustering factor: 20685

Fill distribution:
20-39% = 1
40-59% = 325
60-79% = 5
80-99% = 9

FK_WYSTGRKT_RGRKTRWYS_RODZGRUP
depth: 2
leaf buckets: 284
nodes: 628985

total dup: 628982
max dup: 212258

Clustering factor: 6647

Fill distribution:
40-59% = 212
60-79% = 2
80-99% = 70    

gstat on new database:

WYSTGRKTR (391)
    Primary pointer page: 1502, Index root page: 1503
    Pointer pages: 1, data page slots: 2256
    Data pages: 2256, average fill: 58%
    Primary pages: 2256, secondary pages: 0, swept pages: 2250
    Empty pages: 5, full pages: 2249
    Fill distribution:
         0 - 19% = 5
        20 - 39% = 1
        40 - 59% = 2250
        60 - 79% = 0
        80 - 99% = 0

FK_WYSTGRKT_GRKTRWYST_GRUPAKON

depth: 2

leaf buckets: 199
nodes: 634658

total dup: 634592
max dup: 210763

Clustering factor: 20881

Fill distribution:
40-59% = 26
80-99% = 173

FK_WYSTGRKT_RGRKTRWYS_RODZGRUP

depth: 2
leaf buckets: 189
nodes: 634658

total dup: 634655
max dup: 214149

Clustering factor: 6707

Fill distribution:
40-59% = 5
60-79% = 1
80-99% = 183   

[Tomasz Dubiel]

Plan on the database before restore:

PLAN (K_DOK INDEX (FK_KONTRAH_DOKZEW_KONTRAH))
PLAN JOIN (
  JOIN (
    JOIN (
      JOIN (
        JOIN (
          JOIN (
            JOIN (
              JOIN (
                JOIN (
                  JOIN (
                    JOIN (
                      KT INDEX (FK_KONTRAH_SLOWNIKRODZAJ),
                      KP1 INDEX (PK_KONTRAH_PRIORYTET),
                      WGKF INDEX (MK_WYSTGRKTR_RODZGR),
                      ZGKL INDEX (PK_ZEST_GRUPAKONTRAH_LISTA)
                    ),
                    DK INDEX (MK_DANEKONTRAH_BAZAID)
                  ),
                  PL INDEX (PK_KONTRAH)
                ),
                AK INDEX (PK_AKWIZYTOR)
              ),
              WG1 INDEX (MK_WYSTGRKTR_RODZGR)
            ),
            GK1 INDEX (PK_GRUPAKONTRAH)
          ),
          WG10001 INDEX (MK_WYSTGRKTR_RODZGR)
        ),
        GK10001 INDEX (PK_GRUPAKONTRAH)
      ),
      WG10002 INDEX (MK_WYSTGRKTR_RODZGR)
    ),
    GK10002 INDEX (PK_GRUPAKONTRAH)
  ),
  OZN INDEX (MK_OZNACZDOK)
)

and the plan on the database after restore:

PLAN (K_DOK INDEX (FK_KONTRAH_DOKZEW_KONTRAH))
PLAN JOIN (
  JOIN (
    JOIN (
      JOIN (
        JOIN (
          JOIN (
            JOIN (
              JOIN (
                JOIN (
                  JOIN (
                    JOIN (
                      ZGKL INDEX (MK_ZEST_GRUPAKONTRAH_LISTA_G),
                      WGKF INDEX (
                        FK_WYSTGRKT_GRKTRWYST_GRUPAKON,
                        FK_WYSTGRKT_RGRKTRWYS_RODZGRUP
                      ),
                      KT INDEX (PK_KONTRAH),
                      KP1 INDEX (PK_KONTRAH_PRIORYTET)
                    ),
                    DK INDEX (MK_DANEKONTRAH_BAZAID)
                  ),
                  PL INDEX (PK_KONTRAH)
                ),
                AK INDEX (PK_AKWIZYTOR)
              ),
              WG1 INDEX (MK_WYSTGRKTR_RODZGR)
            ),
            GK1 INDEX (PK_GRUPAKONTRAH)
          ),
          WG10001 INDEX (MK_WYSTGRKTR_RODZGR)
        ),
        GK10001 INDEX (PK_GRUPAKONTRAH)
      ),
      WG10002 INDEX (MK_WYSTGRKTR_RODZGR)
    ),
    GK10002 INDEX (PK_GRUPAKONTRAH)
  ),
  OZN INDEX (MK_OZNACZDOK)
)

[Tomasz Dubiel]

Sorry, I forgot to recalculate statistics after insert from old table. After that nothing changed. I don't know what to do next.

[Tomasz Dubiel]

I tried to simplify the query and got one interesting result:

select 1

from KONTRAH KT
inner join WYSTGRKTR WGKF on (KT.ID_KONTRAH = WGKF.ID_KONTRAH) and
      (WGKF.ID_RODZGRUPKTR = 10001)
inner join ZEST_GRUPAKONTRAH_LISTA ZGKL on (WGKF.ID_GRUPAKONTRAH = ZGKL.ID_GRUPAKONTRAHNAL) and

      (ZGKL.ID_GRUPAKONTRAH = 10080)

where  (KT.NIEHANDLOWYW = 0) and
      ((KT.BAZAKONTRAH = 0) )

no matter what I comment out in where section - then I get the same performance, the same plan:

PLAN JOIN (ZGKL INDEX (MK_ZEST_GRUPAKONTRAH_LISTA_G), WGKF INDEX (FK_WYSTGRKT_GRKTRWYST_GRUPAKON, FK_WYSTGRKT_RGRKTRWYS_RODZGRUP), KT INDEX (PK_KONTRAH))

and indexed reads on both databases.

However, when I run with both conditions, the perfomance drops on the database before restore:

PLAN JOIN (KT INDEX (FK_KONTRAH_SLOWNIKRODZAJ), WGKF INDEX (MK_WYSTGRKTR_RODZGR), ZGKL INDEX (PK_ZEST_GRUPAKONTRAH_LISTA)).

Plan explained before restore:

Select Expression
    -> Nested Loop Join (inner)
        -> Filter
            -> Table "KONTRAH" as "KT" Access By ID
                -> Bitmap
                    -> Index "FK_KONTRAH_SLOWNIKRODZAJ" Range Scan (full match)
        -> Filter
            -> Table "WYSTGRKTR" as "WGKF" Access By ID
                -> Bitmap
                    -> Index "MK_WYSTGRKTR_RODZGR" Unique Scan
        -> Filter
            -> Table "ZEST_GRUPAKONTRAH_LISTA" as "ZGKL" Access By ID
                -> Bitmap
                    -> Index "PK_ZEST_GRUPAKONTRAH_LISTA" Unique Scan
and after restore:

Select Expression
    -> Nested Loop Join (inner)
        -> Filter
            -> Table "ZEST_GRUPAKONTRAH_LISTA" as "ZGKL" Access By ID
                -> Bitmap
                    -> Index "MK_ZEST_GRUPAKONTRAH_LISTA_G" Range Scan (full match)
        -> Filter
            -> Table "WYSTGRKTR" as "WGKF" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_WYSTGRKT_GRKTRWYST_GRUPAKON" Range Scan (full match)
                    -> Bitmap
                        -> Index "FK_WYSTGRKT_RGRKTRWYS_RODZGRUP" Range Scan (full match)
        -> Filter
            -> Table "KONTRAH" as "KT" Access By ID
                -> Bitmap
                    -> Index "PK_KONTRAH" Unique Scan

All statistics for given indexes are the same on both databases:

SELECT
    I.RDB$INDEX_NAME AS INDEX_NAME,
    I.RDB$RELATION_NAME AS TABLE_NAME,
    I.RDB$STATISTICS AS STATISTICS,
    S.RDB$FIELD_NAME AS FIELD_NAME,
    S.RDB$FIELD_POSITION AS FIELD_POS
FROM RDB$INDICES I
LEFT JOIN RDB$INDEX_SEGMENTS S
       ON S.RDB$INDEX_NAME = I.RDB$INDEX_NAME
WHERE I.RDB$INDEX_NAME IN (
    'FK_KONTRAH_SLOWNIKRODZAJ',
    'MK_WYSTGRKTR_RODZGR',
    'PK_ZEST_GRUPAKONTRAH_LISTA',
    'MK_ZEST_GRUPAKONTRAH_LISTA_G',
    'FK_WYSTGRKT_GRKTRWYST_GRUPAKON',
    'FK_WYSTGRKT_RGRKTRWYS_RODZGRUP',
    'PK_KONTRAH'
)
ORDER BY
    I.RDB$INDEX_NAME,
    S.RDB$FIELD_POSITION;

Best regards,

Tomek.

[Tomasz Dubiel]

So, there is not always a way to fix a perfomance problem in other way than backup restore and we need to accept it.