Firebird 6 is 20% slower in read operations than Firebird 5

[C-SICARD]

Hello,

My initial tests show that Firebird 6 is 20% slower in read operations than Firebird 5. Both servers are running on the same machine with the same configuration file and are working on identical small-sized databases (50 MB) after backup and restore (ODS 13 and 14).

[aafemt]

Remote or embedded mode? Natural scan or indexed access?

Do you have a reproducible testcase?

[C-SICARD]

It's a bit tricky — my management app is opening 20% slower. But I'll see if I can isolate a specific query.

[aafemt]

https://firebirdtest.com/ shows that actually the slowdown is much bigger than 20% and current snapshot of Firebird 6 is still quite unstable after schema introduction.

You can use MSVC profiler to get a hot path.

[sim1984]

I have an idea why your application has become slower. It seems to me that the main reason is the introduction of schemes. Your system requests are not designed for the presence of schemes. Yes, if you do not add your own schemes, the application will be compatible, but the system requests themselves will be slower. Just compare:

select *
from rdb$relation_fields rf
where rf.rdb$relation_name = 'SOME_TABLE'

On Firebird 5.0 this will have a plan:

Select Expression
    -> Filter
        -> Table "RDB$RELATION_FIELDS" as "RF" Access By ID
            -> Bitmap
                -> Index "RDB$INDEX_4" Range Scan (full match)

At the same time, in Firebird 6.0 this query will have a plan:

Select Expression
    -> Filter
        -> Table "SYSTEM"."RDB$RELATION_FIELDS" as "RF" Full Scan

The index is no longer used because the index now starts with the schema name:

CREATE INDEX RDB$INDEX_4 ON RDB$RELATION_FIELDS (RDB$SCHEMA_NAME, RDB$RELATION_NAME);

If you change the original query to explicitly specify the schema, the query becomes fast again:

select *
from rdb$relation_fields rf
where rf.rdb$schema_name = 'PUBLIC'
  and rf.rdb$relation_name = 'SOME_TABLE'

Select Expression
    -> Filter
        -> Table "SYSTEM"."RDB$RELATION_FIELDS" as "RF" Access By ID
            -> Bitmap
                -> Index "SYSTEM"."RDB$INDEX_4" Range Scan (full match)

This is of course not the ultimate truth, but just an assumption. You need to run a trace and see what actually started to run slower.

P.S. Of course, you can create your own indexes for system tables, but this is not a good solution, since these indexes are not saved after backup/restore.

[EPluribusUnum]

@sim1984 , FB60 system table indexes were changed such way, that now it is not backward comatible, leads to index loss? That is a huge problem. You should open a new issure for that.

[aafemt]

The better solution would be to create a set of views for compatibility with old applications. Something like

CREATE VIEW PUBLIC.RDB$RELATION_FIELDS AS
SELECT
  RDB$FIELD_NAME,
  RDB$RELATION_NAME,
  RDB$FIELD_SOURCE,
  RDB$QUERY_NAME,
  RDB$BASE_FIELD,
  RDB$EDIT_STRING,
  RDB$FIELD_POSITION,
  RDB$QUERY_HEADER,
  RDB$UPDATE_FLAG,
  RDB$FIELD_ID,
  RDB$VIEW_CONTEXT,
  RDB$DESCRIPTION,
  RDB$DEFAULT_VALUE,
  RDB$SYSTEM_FLAG,
  RDB$SECURITY_CLASS,
  RDB$COMPLEX_NAME,
  RDB$NULL_FLAG,
  RDB$DEFAULT_SOURCE,
  RDB$COLLATION_ID,
  RDB$GENERATOR_NAME,
  RDB$IDENTITY_TYPE
FROM SYSTEM.RDB$RELATION_FIELDS
WHERE RDB$SCHEMA_NAME=CURRENT_SCHEMA

[sim1984]

@sim1984 , FB60 system table indexes were changed such way, that now it is not backward comatible, leads to index loss? That is a huge problem. You should open a new issure for that.

There are no index losses. It's just that the indexes on the system tables are defined differently (on other sets of fields). It would be good to leave the previous indexes as well, so that queries to the system tables in old applications are executed at the same speed without rewriting them.

[aafemt]

It would be good to leave the previous indexes as well

Swapping of the fields would be enough. I.e. RDB$RELATION_NAME,RDB$SCHEMA_NAME instead of RDB$SCHEMA_NAME,RDB$RELATION_NAME (let alone usage of schema id instead of name).

[EPluribusUnum]

@sim1984, I ment the loss from plan point of view, not that it was dropped. Put RDB$SCHEMA_NAME at the end of the indicies, not the begining. Should be backward compatible that way, also new system queries should be able to use it.

[sim1984]

@sim1984, I ment the loss from plan point of view, not that it was dropped. Put RDB$SCHEMA_NAME at the end of the indicies, not the begining. Should be backward compatible that way, also new system queries should be able to use it.

I'm not sure this is a good idea. For the RDB$RELATIONS table, you may need to show all tables according to the selected schema. And then your inverted index will backfire. I think you need to create the indexes that exist now and add indexes for queries from Legacy applications. And this should be done at the level of database creation, not manually.

[mrotteveel]

It would be good to leave the previous indexes as well

Swapping of the fields would be enough. I.e. RDB$RELATION_NAME,RDB$SCHEMA_NAME instead of RDB$SCHEMA_NAME,RDB$RELATION_NAME (let alone usage of schema id instead of name).

Doing so might potentially deoptimize other queries, though.

[asfernandes]

Doing so might potentially deoptimize other queries, though.

Yes. DROP SCHEMA, for example.

I can agree to add extra indexes with the order of the fields inversed, but to be removed in v7.

It will make changes to the system tables slower.

[hvlad]

What query could need index on (relname, schema) fields ? Just add "old" index on (relname), and so on...

[mrotteveel]

Having an index with only RDB$RELATION_NAME should be enough, I'd think. I'm not sure you'd really need (RDB$RELATION_NAME, RDB$SCHEMA_NAME) in addition to (RDB$SCHEMA_NAME, RDB$RELATION_NAME)

The same probably goes for all tables of objects that received RDB$SCHEMA_NAME. I do think that is something that should remain, because there are plenty of situations where one might query only by relation name, even for applications that are schema-aware.

[mrotteveel]

there are plenty of situations where one might query only by relation name, even for applications that are schema-aware.

As an example of this, in the JDBC DatabaseMetaData API, the value null for schema or schemaPattern means "[..] that the schema name should not be used to narrow the search", which leads to Jaybird generating queries with only the object name and not the schema name if it's null.

[sim1984]

There is no need to invent new indexes, you need to add the same indexes that were in ODS 13 (before the introduction of the schemes). With only one difference, all these indexes must be non-unique.