[ 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