You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
GSTAT: values 'leaf buckets' and 'nodes' remained non-zero in index statistics after transaction rollback and re-connect and fetching all records from (now empty) table [CORE4778]
#5077
set term ^;
execute block as
declare n int = 5000;
begin
while (n>0) do insert into t1(bx) values( rpad( 'QWERTY', 250, uuid_to_char(gen_uuid()) ) ) returning :n-1 into n;
end
^
set term ;^
The case assumes that there is "zero cost" to an index (especially one where data was previously present). The gstat details for the index, had the data been committed, would have been something like:
Index T1\_IDX \(0\)
Depth: 3, leaf buckets: 251, nodes: 5000
Average data length: 242\.02, total dup: 0, max dup: 0
Fill distribution:
0 \- 19% = 14
20 \- 39% = 3
40 \- 59% = 87
60 \- 79% = 102
80 \- 99% = 45
So, the fact that the index values are not zero is not a significant/meaningful problem, IMO.
Submitted by: @pavel-zotov
Test (file name = `idx_gstat.sql`):
shell del C:\MIX\firebird\QA\fbt-repo\tmp\gstat_idx_test.fdb 2>nul;
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\gstat_idx_test.fdb';
commit;
recreate table t1 ( bx varchar(250) );
create index t1_idx on t1(bx);
commit;
set term ^;
execute block as
declare n int = 5000;
begin
while (n>0) do insert into t1(bx) values( rpad( 'QWERTY', 250, uuid_to_char(gen_uuid()) ) ) returning :n-1 into n;
end
^
set term ;^
rollback;
connect 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\gstat_idx_test.fdb';
out nul;
select count(*) cnt_idx from t1 where bx>='';
commit;
select * from t1 where bx>='' order by bx;
commit;
out;
Run:
step-1: isql -q -i idx_gstat.sql
step-1: gstat.exe -r localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\GSTAT_IDX_TEST.FDB >gstat_r.txt
In the log of gstat one may see:
T1 (128)
Primary pointer page: 211, Index root page: 212
Total formats: 1, used formats: 0
Average record length: 0.00, total records: 0
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: 0.00, compression ratio: 0.00
Pointer pages: 1, data page slots: 0
Data pages: 0, average fill: 0%
Primary pages: 0, secondary pages: 0, swept pages: 0
Empty pages: 0, full pages: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Why values 'leaf buckets' and 'nodes' remained non-zero ?
The text was updated successfully, but these errors were encountered: