Descending index could be very inefficient for some keys
--------------------------------------------------------
Key: CORE-4302
URL: http://tracker.firebirdsql.org/browse/CORE-4302
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 1, 2.5.2 Update 1
Reporter: Vlad Khorsun
Using FB3, default config (SS, page cache 2048, database page size is 4096).
Prepare data
set term ^;
create table t1 (id int, val int)
^
execute block as
declare i int = 0;
begin
while (i < 1000000) do
begin
insert into t1 values (:i, mod(:i, 10));
i = i + 1;
end
end
^
commit
^
create descending index t1_desc_idx on t1 (val)
^
set stat on
^
Issue 1: insert three records and look at stats
insert into t1 values (100, 1)^
Buffers = 2048
Reads = 0
Writes 0
Fetches = 7
SQL> insert into t1 values (100, 2)^
Buffers = 2048
Reads = 0
Writes 0
Fetches = 386
SQL> insert into t1 values (100, 3)^
Buffers = 2048
Reads = 0
Writes 0
Fetches = 7
There was 7 fetches for keys "1" and "3" while 386 fetches for key "2" !
Another example (on the same data):
select * from t1 where val <= 1 order by val desc rows 1^
ID VAL
============ ============
1 1
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 2
Writes 0
Fetches = 7
select * from t1 where val <= 2 order by val desc rows 1^
ID VAL
============ ============
2 2
Elapsed time= 0.64 sec
Buffers = 2048
Reads = 28576
Writes 4
Fetches = 645359
select * from t1 where val <= 3 order by val desc rows 1^
ID VAL
============ ============
3 3
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 4
Writes 0
Fetches = 7
Again, key "2" produced very poor performance comparing with another keys
--
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
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
