Garbage in the compound index after several updates
---------------------------------------------------
Key: CORE-5877
URL: http://tracker.firebirdsql.org/browse/CORE-5877
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.3, 2.5.8
Reporter: Dmitriy Starodubov
create table test(
id numeric(15),
state smallint,
priority smallint);
create descending index idx_desc on test(priority, state);
set term ^;
execute block as
declare variable i integer;
begin
i = 0;
while (i < 1000) do begin
insert into test (id, state, priority)
values(:i, 1, 1);
i = i + 1;
end
end^
After this "gstat -r" shows expected 1000 records and 1000 index nodes.
But after several updates too many nodes are created.
update test set state=2;
update test set state=3;
commit;
I repeat it 5 times and get such statistics:
TEST (128)
Primary pointer page: 166, Index root page: 167
Average record length: 13.74, total records: 1000
Average version length: 13.74, total versions: 1000, max versions: 1
...
Index IDX_DESC (0)
Depth: 2, leaf buckets: 5, nodes: 5000
Average data length: 0.01, total dup: 4999, max dup: 4999
And these nodes are not deleted after cleaning table and garbage collecting.
SQL> delete from test;
SQL> commit;
SQL> select * from test;
TEST (128)
Primary pointer page: 166, Index root page: 167
Average record length: 0.00, total records: 0
Average version length: 0.00, total versions: 0, max versions: 0
...
Index IDX_DESC (0)
Depth: 2, leaf buckets: 5, nodes: 4000
Average data length: 0.01, total dup: 3999, max dup: 3999
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
