4.0.0 - Performance degradation after index statistics refresh

[Gabor Boros]

Hi All,

I have a database and execute a block on it. The statistics in ISQL:

Current memory = 67544096
Delta memory = 28578768
Max memory = 84687184
Elapsed time = 21.448 sec
Buffers = 2048
Reads = 7763
Writes = 32990
Fetches = 9129231

Go back to the original state of the database, refresh the index
statistics and execute the block. After that the statistics in ISQL:

Current memory = 80310720
Delta memory = 17157792
Max memory = 108855120
Elapsed time = 43.800 sec
Buffers = 2048
Reads = 7016
Writes = 33039
Fetches = 97744917

With 3.0.7 the numbers ~equivalent before and after the statistics
refresh, in example:

Current memory = 106627792
Delta memory = 39031568
Max memory = 110498496
Elapsed time= 17.805 sec
Buffers = 2048
Reads = 6343
Writes = 18500
Fetches = 8897661

I cannot share the test case in the public but can send privately to a
core developer.

The index refresher is:

EXECUTE BLOCK AS
declare variable index_name VARCHAR(31);
BEGIN
for select RDB$INDEX_NAME from RDB$INDICES into :index_name do
execute statement 'SET statistics INDEX ' || :index_name || ';';
END

Something wrong with it from 4.0 POV or is this a bug?

Gabor

[Gabor Boros]

2021.06.02. 11:37 keltezéssel, Gabor Boros írta:
> Hi All,


Meanwhile identified what index is the source of the problem.
The slowdown appears too if execute just a "SET statistics INDEX
RDB$INDEX_27;" before the block. Any idea why?

SQL> select rdb$statistics from rdb$indices where
rdb$index_name='RDB$INDEX_27';

RDB$STATISTICS
=======================
0.000000000000000

SQL> SET statistics INDEX RDB$INDEX_27;
SQL> commit;
SQL> select rdb$statistics from rdb$indices where
rdb$index_name='RDB$INDEX_27';

RDB$STATISTICS
=======================
8.996851101983339e-05

Gabor

[liviuslivius]

You must look at particular statements inside this execute block. And look at query plan changing.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Gabor Boros <mlng...@bgss.hu>

Data: 02.06.2021 14:58 (GMT+01:00)

Do: firebird...@googlegroups.com

Temat: Re: [firebird-support] 4.0.0 - Performance degradation after index statistics refresh

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/2c9eca4d-b328-905b-19f7-5148302091ee%40bgss.hu.

[Gabor Boros]

2021.06.02. 15:05 keltezéssel, liviuslivius írta:
> You must look at particular statements inside this execute block. And
> look at query plan changing.
>
> Regards,
> Karol Bieniaszewski

Plans are same before and after. But the execution time doubled and
Fetches more than ten times.

Gabor