[
http://tracker.firebirdsql.org/browse/CORE-4302?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vlad Khorsun reopened CORE-4302:
--------------------------------
Re-opened to fix the new issue found by Pavel.
While it is different from the code POV it also could be formulated as
"indefficient descending index scan for some values".
> 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: 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1
> Reporter: Vlad Khorsun
> Assignee: Vlad Khorsun
> Fix For: 2.5.3, 3.0 Alpha 2, 2.1.6
>
>
> Big thanks to the Pavel Zotov who found the issue and helped with testing.
> 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
