[ 
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

Reply via email to