Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Apr 28, 2015 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

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

Index T1\_IDX \(0\)
Root page: 227, depth: 2, leaf buckets: 3, nodes: 16 <<<<<<<<<<<<<<<<<<<  ?? <<<<<<<<<<<<<<<
Average node length: 249\.13, total dup: 0, max dup: 0
Average key length: 247\.00, compression ratio: 1\.01
Average prefix length: 6\.88, average data length: 243\.13
Clustering factor: 16, ratio: 1\.00
Fill distribution:
     0 \- 19% = 1
    20 \- 39% = 1
    40 \- 59% = 0
    60 \- 79% = 1
    80 \- 99% = 0

Why values 'leaf buckets' and 'nodes' remained non-zero ?

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants